Sql Server Trigger 的使用(Insert ,Update,Delete...)
来源:互联网 发布:win7系统玩游戏优化 编辑:程序博客网 时间:2024/05/17 01:21
Table Name: TB_1,TB_2 (两个table 结构完全一样)
Create Table TB_1(ID Int, Name_1 varchar(20),Status varchar(20) ,Type Varchar(20))
Create Table TB_2(ID Int, Name_1 varchar(20),Status varchar(20) ,Type Varchar(20))
-- 为Tb_1创建Trigger, 当 TB_1 发生变化时,变化的信息(Insert ,Update,Delete)将保存于 TB_2 中,Update是两条记录,Update 之前和之后 都要保存盐类
Create trigger Trig_t2
On Tb_1
For Insert,Update,Delete
As
declare @id int,
@name_1 varchar(20),
@status varchar(20)
If not exists(Select 1 from deleted) ----Insert
Begin
select @id=id, @name_1=name_1,@status=status from inserted
insert into tb_2(id,name_1,status,type)values(@id,@name_1,@status,'Insert')
End
If exists(select 1 from inserted) and exists(select 1 from deleted) ---Update
Begin
/* Update Before */
select @id=id, @name_1=name_1,@status=status from deleted
insert into tb_2(id,name_1,status,type)values(@id,@name_1,@status,'UpdateBefor')
/* Update End */
select @id=id, @name_1=name_1,@status=status from inserted
insert into tb_2(id,name_1,status,type)values(@id,@name_1,@status,'UpdateEnd')
End
If not exists(select 1 from inserted) ---Delete
Begin
select @id=id, @name_1=name_1,@status=status from inserted
insert into tb_2(id,name_1,status,type)values(@id,@name_1,@status,'Delete')
End
----测试----
insert into tb_1(id,name_1,status)values('1','1','1')
select * from tb_1
select * from tb_2
id name_1 status type
----------- -------------------- -------------------- --------------------------------------------------
1 1 1 NULL
(1 row(s) affected)
id name_1 status type
----------- -------------------- -------------------- --------------------------------------------------
1 1 1 Insert
-----------测试Update---------------
update tb_1 set name_1='2'where id='1'
select * from tb_1
select * from tb_2
id name_1 status type
----------- -------------------- -------------------- --------------------------------------------------
1 2 1 NULL
(1 row(s) affected)
id name_1 status type
----------- -------------------- -------------------- --------------------------------------------------
1 1 1 Insert
1 1 1 UpdateBefor
1 2 1 UpdateEnd
- Sql Server Trigger 的使用(Insert ,Update,Delete...)
- [SQL Server] TRIGGER 触发器获得insert, delete, update行的信息
- SqlServer Trigger insert update delete
- Sqlserver Trigger(Insert,Update,Delete)
- sql server 带有OUTPUT的INSERT,DELETE,UPDATE
- sql server 带有OUTPUT的INSERT,DELETE,UPDATE
- SQL 表的Insert ,update ,delete 触发器
- 在SQL Server中使用merge同时进行Insert,Update和Delete操作
- SQL Server索引进阶第十三篇:Insert,Update,Delete语句
- SQL Server索引进阶第十三篇:Insert,Update,Delete语句
- SQL SERVER insert/delete/update 中的 ouput语句
- SQL SERVER INSERT、UPDATE 、DELETE、SELECT增、改、删、查
- SQL server触发器中 update insert delete 示例
- sql-server基础三(select 、update、insert,delete)
- [学习SQL SERVER 2005系列]关于INSERT、UPDATE 或 DELETE的OUTPUT及OUTPUT...INTO...用法
- SQL Server--OUTPUT及OUTPUT...INTO... 在 INSERT、UPDATE 或 DELETE的用法
- SQL Server:触发器品读 -- Insert、 Delete、 Update(“updated ”是不存在的,不能想当然)
- SQL Server--OUTPUT及OUTPUT...INTO... 在 INSERT、UPDATE 或 DELETE的用法
- 取随机数时的小技巧
- java字符编码原理解析
- C#中的"序列化" Serializable 理解
- 加速你的Eclipse
- Java与.NET 的Web Services相互调用
- Sql Server Trigger 的使用(Insert ,Update,Delete...)
- Rational Rose2003安装体验
- outputstream inputstream
- 更新DiskFileUpload到ServletFileUpload
- SQL Create View
- Multilingual NetBeans RCP
- NetBeans Splash Screen 加入 Login Dialog
- Getting Started 系列 - CallbackSystemAction 演練
- c语言库函数strtol fprintf memcpy memset sizeof