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 a=@agoINSERT INTO TestTru (a,b) VALUES('a', 'b')DECLARE @rt VARCHAR(6)EXECUTE testProc 'a', @b=@rt 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?