SQLSERVER触发器内INSERT,UPDATE,DELETE三种状态
来源:互联网 发布:知彼定位 编辑:程序博客网 时间:2024/04/27 21:58
CREATE TRIGGER tr_T_A ON T_A for INSERT,UPDATE,DELETE
如IF exists (select * from inserted) and not exists (select * from deleted) 则为 INSERT
如IF exists(select * from inserted ) and exists (select * from deleted) 则为 UPDATE
如IF exists (select * from deleted) and not exists (select * from inserted)则为 DELETE
插入操作(Insert):Inserted表有数据,Deleted表无数据
删除操作(Delete):Inserted表无数据,Deleted表有数据
更新操作(Update):Inserted表有数据(新数据),Deleted表有数据(旧数据)
笔者用到的案例:
create TRIGGER [risk].[Entry_Head_port_Exchange_Trigger]
ON [RiskH800].[risk].[ENTRY_HEAD]
AFTER INSERT,UPDATE
AS
DECLARE @COUNT INT
DECLARE @MANUAL_NO_COUNT INT
IF EXISTS (SELECT 1 FROM INSERTED)
IF EXISTS(SELECT 1 FROM DELETED)
BEGIN
SELECT @COUNT = COUNT(*) FROM risk.ENTRY_HEAD_port_EXCHANGE WHERE ENTRY_ID = (select ENTRY_ID FROM DELETED)
SELECT @MANUAL_NO_COUNT = COUNT(MANUAL_NO) FROM DELETED where substring(MANUAL_NO,1,1)= 'B' or substring(MANUAL_NO,1,1)='C'
IF @COUNT <=0 AND @MANUAL_NO_COUNT>0
INSERT INTO risk.ENTRY_HEAD_port_EXCHANGE(ENTRY_ID,CREATE_DATE)(SELECT ENTRY_ID,getdate() FROM DELETED)
END
ELSE
BEGIN
SELECT @COUNT = COUNT(*) FROM risk.ENTRY_HEAD_port_EXCHANGE WHERE ENTRY_ID = (select ENTRY_ID FROM INSERTED)
SELECT @MANUAL_NO_COUNT = COUNT(MANUAL_NO) FROM INSERTED where substring(MANUAL_NO,1,1)= 'B' or substring(MANUAL_NO,1,1)='C'
IF @COUNT <=0 AND @MANUAL_NO_COUNT>0
INSERT INTO risk.ENTRY_HEAD_port_EXCHANGE(ENTRY_ID,CREATE_DATE)(SELECT ENTRY_ID,getdate() FROM INSERTED)
END
0 0
- SQLSERVER触发器内INSERT,UPDATE,DELETE三种状态
- SQLSERVER触发器内INSERT,UPDATE,DELETE三种状态
- SQLSERVER触发器触发INSERT,UPDATE,DELETE三种状态
- 一个触发器内三种INSERT,UPDATE,DELETE状态
- 一个触发器内三种INSERT,UPDATE,DELETE状态
- Insert , Update , Delete 触发器
- SqlServer Trigger insert update delete
- Sqlserver Trigger(Insert,Update,Delete)
- SQL 表的Insert ,update ,delete 触发器
- 触发器 判断insert , update ,delete 的方法
- 创建INSERT、DELETE、UPDATE 触发器示例
- 单个触发器判断Insert/Delete/Update操
- sql触发器(insert,update,delete)
- sql触发器(insert,update,delete…
- 创建INSERT、DELETE、UPDATE 触发器示例 .
- sql触发器(insert,update,delete)
- SQL触发器编程(insert/update/delete)
- 1, insert触发器; 2, update触发器; 3, delete触发器;
- C#将wps表格读到datatable中并显示在页面
- 大话设计模式15----抽象工厂模式
- warning C4305:“初始化”:从“double”到“float”截断
- Android 中 Canvas 绘图之 Shader 使用图文详解
- Linux - ncat ssl backdoor
- SQLSERVER触发器内INSERT,UPDATE,DELETE三种状态
- mysql命令行设置支持blob大数据的导入
- uva 10010
- Android 中 Canvas 绘图之 PorterDuffXfermode 使用及工作原理详解
- Android的消息机制
- 当errno = ENITR
- ---Jsp(四)page指令
- Mysql数据库的SQL语句
- 周期串 Periodic Strings, UVa455