sqlserver 的简单存储过程和触发器example

简单的sqlserver 2000 存储过程和触发器的例子

 

--用于测试的表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestTru]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TestTru]
GO

CREATE TABLE [dbo].[TestTru] (
	[a] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[b] [char] (10) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

--简单的存储过程

IF EXISTS (SELECT name FROM sysobjects WHERE name='testProc' AND type='P')
DROP PROCEDURE testProc
go
CREATE PROC testProc
@a VARCHAR(10),
@b VARCHAR(6) output
AS
    SELECT @b = b FROM TestTru WHERE a=@a
go

INSERT INTO TestTru (a,b) VALUES('a', 'b')

DECLARE @rt VARCHAR(6)
EXECUTE testProc 'a', @b=@rt OUTPUT
PRINT @rt



--简单的触发器
if exists (SELECT name FROM sysobjects WHERE name='TruTestTru' AND type='TR')
drop Trigger TruTestTru
go

Create Trigger TruTestTru                                                                  
      On TestTru                        --在Student表中创建触发器
      for Update                         --为什么事件触发
    As                                       --事件触发后所要做的事情
      if Update(a)           
      begin

        Update TestTru
          Set b='after Tri'
          From TestTru tt , Deleted  d ,Inserted i     --Deleted和Inserted临时表
          Where tt.a=i.a

      end
go

insert into TestTru (a,b) values('a', 'b')
update TestTru set a='a1' where a='a'
select * from TestTru
delete TestTru