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]GOCREATE 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 testProcgoCREATE PROC testProc@a VARCHAR(10),@b VARCHAR(6) outputAS    SELECT @b = b FROM TestTru WHERE [email protected]goINSERT INTO TestTru (a,b) VALUES('a', 'b')DECLARE @rt VARCHAR(6)EXECUTE testProc 'a', @[email protected] OUTPUTPRINT @rt--简单的触发器if exists (SELECT name FROM sysobjects WHERE name='TruTestTru' AND type='TR')drop Trigger TruTestTrugoCreate 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      endgoinsert into TestTru (a,b) values('a', 'b')update TestTru set a='a1' where a='a'select * from TestTrudelete TestTru

?