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
==================================================================================
这是个比较简单的例子,我再写个复杂点的例子,TB1 和TB2 是两个主表,TB3 是从表,其TYPE 字段决定引用哪个表,这里三个表都加了触发器,用来维护三表之间的引用完整性,提示信息业根据记录情况变化:
-- 测试表
CREATE TABLE TB1(
ID INT,
INF1 VARCHAR(10)
)
GO
CREATE TABLE TB2(
ID INT,
INF2 VARCHAR(10)
)
GO
CREATE TABLE TB3(
ID INT,
INF3 VARCHAR(10),
TYPE INT,
RID INT
)
GO
-- 触发器
CREATE TRIGGER TR_TB1 ON TB1
FOR DELETE,UPDATE -- 只需要检查删改操作
AS
SET NOCOUNT ON
DECLARE @MSG VARCHAR(200)
IF NOT EXISTS (SELECT 1 FROM INSERTED) -- 删除操作
BEGIN
SELECT @MSG=ISNULL(@MSG+',','')+CAST(D.ID AS VARCHAR)+'('+D.INF1+') 被TB3 记录'+CAST(A.ID AS VARCHAR)+'('+A.INF3+') 引用'
FROM TB3 A,DELETED D
WHERE A.RID=D.ID -- 删除的纪录被TB3 引用
AND A.TYPE=1 -- 引用TB1
IF @MSG IS NOT NULL
BEGIN
SET @MSG=' 不能删除TB1 记录'+@MSG
RAISERROR (@MSG, 16, 1)
ROLLBACK TRANSACTION
RETURN
END
END
IF EXISTS (SELECT 1 FROM INSERTED) -- 修改操作
AND UPDATE(ID) -- 修改了ID
BEGIN
SELECT @MSG=ISNULL(@MSG+',','')+CAST(D.ID AS VARCHAR)+'('+D.INF1+') 的ID 被TB3 记录'+CAST(A.ID AS VARCHAR)+'('+A.INF3+') 引用'
FROM TB3 A,DELETED D
WHERE A.RID=D.ID -- 删除的纪录被TB3 引用
AND A.TYPE=1 -- 引用TB1
IF @MSG IS NOT NULL
BEGIN
SET @MSG=' 不能修改TB1 记录'+@MSG
RAISERROR (@MSG, 16, 1)
ROLLBACK TRANSACTION
RETURN
END
END
GO
-- 触发器
CREATE TRIGGER TR_TB2 ON TB2
FOR DELETE,UPDATE -- 只需要检查删改操作
AS
SET NOCOUNT ON
DECLARE @MSG VARCHAR(200)
IF NOT EXISTS (SELECT 1 FROM INSERTED) -- 删除操作
BEGIN
SELECT @MSG=ISNULL(@MSG+',','')+CAST(D.ID AS VARCHAR)+'('+D.INF2+') 被TB3 记录'+CAST(A.ID AS VARCHAR)+'('+A.INF3+') 引用'
FROM TB3 A,DELETED D
WHERE A.RID=D.ID -- 删除的纪录被TB3 引用
AND A.TYPE=2 -- 引用TB2
IF @MSG IS NOT NULL
BEGIN
SET @MSG=' 不能删除TB2 记录'+@MSG
RAISERROR (@MSG, 16, 1)
ROLLBACK TRANSACTION
RETURN
END
END
IF EXISTS (SELECT 1 FROM INSERTED) -- 修改操作
AND UPDATE(ID) -- 修改了ID
BEGIN
SELECT @MSG=ISNULL(@MSG+',','')+CAST(D.ID AS VARCHAR)+'('+D.INF2+') 的ID 被TB3 记录'+CAST(A.ID AS VARCHAR)+'('+A.INF3+') 引用'
FROM TB3 A,DELETED D
WHERE A.RID=D.ID -- 删除的纪录被TB3 引用
AND A.TYPE=2 -- 引用TB2
IF @MSG IS NOT NULL
BEGIN
SET @MSG=' 不能修改TB2 记录'+@MSG
RAISERROR (@MSG, 16, 1)
ROLLBACK TRANSACTION
RETURN
END
END
GO
-- 触发器
CREATE TRIGGER TR_TB3 ON TB3
FOR INSERT,UPDATE -- 需要检查增改操作,不需要检查删除
AS
SET NOCOUNT ON
DECLARE @MSG VARCHAR(200)
-- 不必分操作类型检测
SELECT @MSG=ISNULL(@MSG+',','')+CAST(I.ID AS VARCHAR)+'('+I.INF3+')'
FROM INSERTED I
WHERE NOT EXISTS (
SELECT 1
FROM TB1 T
WHERE I.RID=T.ID
AND I.TYPE=1 -- 引用TB1
)
AND NOT EXISTS (
SELECT 1
FROM TB2 T
WHERE I.RID=T.ID
AND I.TYPE=2 -- 引用TB2
)
IF @MSG IS NOT NULL
BEGIN
SET @MSG=' 不能增加或者修改TB3 记录'+@MSG+', 没有主表记录'
RAISERROR (@MSG, 16, 1)
ROLLBACK TRANSACTION
RETURN
END
GO
-- 测试
-- 准备数据
INSERT TB1
SELECT 1,'AAA'
UNION ALL SELECT 2,'BBB'
GO
INSERT TB2
SELECT 1,'CCC'
UNION ALL SELECT 3,'DDD'
GO
INSERT TB3
SELECT 1,'EEE',1,1
UNION ALL SELECT 2,'FFF',1,2
UNION ALL SELECT 3,'GGG',2,1
UNION ALL SELECT 4,'HHH',2,3
GO
-- 结果 到现在没有问题
--TB1 删改(TB2 操作基本相同)
DELETE TB1
WHERE ID=1
GO
结果