工作时用到触发器要支持sqlserver 写的Demo 纪念下

发布时间：2020-12-12 13:17:24 所属栏目：MsSql教程来源：网络整理

导读：学习连接： http://www.cnblogs.com/hoojo/archive/2011/07/20/2111316.html ? Demo1：简单的触发器 ? CREATE TRIGGER D_EFILE1_0008_TRIGGER? ?ON D_EFILE1_0008 ?AFTER INSERT,DELETE ?AS ??declare @SYSCODE varchar(32),@UNITSYS varchar(32); ??if (exi

学习连接： http://www.cnblogs.com/hoojo/archive/2011/07/20/2111316.html ? Demo1：简单的触发器 ? CREATE TRIGGER D_EFILE1_0008_TRIGGER?
?ON D_EFILE1_0008
?AFTER INSERT,DELETE
?AS
??declare @SYSCODE varchar(32),@UNITSYS varchar(32);
??if (exists (select 1 from inserted) and not exists (select 1 from deleted))
???BEGIN?
????select @SYSCODE = SYSCODE,@UNITSYS=UNITSYS from INSERTED;
????INSERT INTO INCTABLE VALUES(@SYSCODE,'1',2,@UNITSYS,convert(char(19),getdate(),20));
???END
??else if (not exists (select 1 from inserted) and exists (select 1 from deleted))
???BEGIN
????select @SYSCODE = SYSCODE,@UNITSYS=UNITSYS from deleted;
????INSERT INTO INCTABLE VALUES(@SYSCODE,1,20));
???END ? ? ? ? Demo2：复杂点的触发器 ? CREATE TRIGGER D_FILE1_0008_TRIGGER?ON D_FILE1_0008 FOR UPDATE AS ??IF UPDATE(STATUS) ? ???declare @SYSCODE varchar(32),@UNITSYS varchar(32),@STATUS INT,@ESYSCODE varchar(32); ???begin ????select @SYSCODE = SYSCODE,@UNITSYS=UNITSYS,@STATUS=STATUS from INSERTED; ???end ???DECLARE ESYSCODES CURSOR FOR? select SYSCODE from D_EFILE1_0008? WHERE PSYSCODE=(select SYSCODE from INSERTED); ???open ESYSCODES; ???FETCH NEXT FROM ESYSCODES ???INTO @ESYSCODE ???WHILE @@FETCH_STATUS = 0 ????BEGIN ?????IF (@STATUS=1) ??????BEGIN ???????INSERT INTO INCTABLE VALUES(@ESYSCODE,20)); ??????end ?????else if(@STATUS=0) ??????BEGIN? ???????INSERT INTO INCTABLE VALUES(@ESYSCODE,20)); ??????end? ?????FETCH NEXT FROM ESYSCODES ?????INTO @ESYSCODE ????END ???CLOSE ESYSCODES; ???DEALLOCATE ESYSCODES;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!