简单的删除触发器,把删除的记录记到另外一张表
原表A,目标表B
删除表A的记录时,表A中的四个字段(f1,f2,f3,f4)保存到B表去
就是保存时不要重复保存在B表,f1,f2,f3为组合关键字
原表A,目标表B
删除表A的记录时,表A中的四个字段(f1,f2,f3,f4)保存到B表去
就是保存时不要重复保存在B表,f1,f2,f3为组合关键字
解决方案 »
- Sql server 2008 授权问题!!
- 初学者:请教一下
- 再安装sql server的时候哪里可以设置表里面数据去分大小写啊!谢谢
- 发一个小题目
- 查询消息汇总
- 忽略了数据库名XXX,将引用 tempdb 中的对象 这种错误太奇怪了
- 多条记录并为变成一条记录(有点怪)
- Dephi写的C/S程序,怎样有效防止SQL注入
- 用conn.execute "if object_id('mydb..#tmp') is not null drop table #tmp" 为何删不掉临时表?
- 一个一直没有很好解决得问题,给出我全部得分数求方法,
- 如何修改 MSSQL2005 的默认编码(字符集)?
- SqlServer 与 MySql 的 sql 语句最大的区别
ON a
for delete as
?????????
CREATE TRIGGER [TRIGGER_NAME] ON [DBO].[TABLE]
FOR DELETE
AS
BEGIN SET NOCOUNT ON
INSERT INTO B (f1,f2,f3,f4)
SELECT f1,f2,f3,f4
FROM DELETEED
WHERE f1+f2+f3+f4 not in (select f1+f2+f3+f4 from B)
SET NOCOUNT OFFEND
insert into B
select f1,f2,f3,f4 from B right join Deleted as d on B.f1=d.f1 and B.f2=d.f2 and B.f3=d.f3
where B.f1 is null
--分真多CREATE TRIGGER trgDel ON TableName
FOR DELETE
AS
INSERT INTO B(A,B,C,D)
SELECT a.A,a.B,a.C,a.D
FROM deleted a LEFT JOIN B
ON a.A =b.A
AND a.B=b.B
AND a.C=b.C
AND a.D=b.D
WHERE b.A IS NULL
CREATE TRIGGER TRIGGER_NAME
ON A
FOR DELETE
AS
insert into B(f1,f2,f3,f4)
select f1,f2,f3,f4 from B right join Deleted as d on B.f1=d.f1 and B.f2=d.f2 and B.f3=d.f3
where B.f1 is nullGO
FOR DELETE
AS
BEGIN SET NOCOUNT ON
INSERT INTO B (f1,f2,f3,f4)
SELECT f1,f2,f3,f4
FROM DELETED
WHERE f1+f2+f3+f4 not in (select f1+f2+f3+f4 from B)
SET NOCOUNT OFFEND
ON A
FOR DELETE
AS
insert into B(f1,f2,f3,f4)
select d.f1,d.f2,d.f3,d.f4 from B right join Deleted as d on B.f1=d.f1 and B.f2=d.f2 and B.f3=d.f3
where B.f1 is nullGO
FOR DELETE
AS
INSERT INTO B(A,B,C,D)
SELECT a.A,a.B,a.C,a.D
FROM deleted a LEFT JOIN B
ON a.A =b.A
AND a.B=b.B
AND a.C=b.C
AND a.D=b.D
WHERE b.A IS NULL and b.B is null and b.c is null这样基本没有问题实在不行 在加一个not exists
尽量不要使用组合关键字
加个自动增加的做主键都可以
这样的话我触发判断都没个依据
CREATE TRIGGER tri_del ON a
FOR DELETE
AS
BEGIN
INSERT INTO B
SELECT f1,f2,f3,f4
FROM A
WHERE id='''''
END
create trigger tri_A on A for delete
as
insert into B(f1,f2,f3,f4) select f1,f2,f3,f4 from deleted
go
create trigger tb1_delete
on tb1
for delete
as
if not exists(
select 1
From table2
Where f1 = (select f1 from deleted) And
f2 = (select f2 from deleted) And
f3 = (select f3 from deleted)
)
Begin
INSERT INTO table2
Select * From DeletedEnd
ON A
FOR DELETE
AS
insert into B(f1,f2,f3,f4)
select d.f1,d.f2,d.f3,d.f4 from B right join Deleted as d on B.f1=d.f1 and B.f2=d.f2 and B.f3=d.f3
where B.f1 is nullGO
create trigger A_delete on A
for delete
as
insert B(f1,f2,f3,f4)
select f1,f2,f3,f4 from deleted d
where not exists (select 1 from B where f1=d.f1 and f2=d.f2 and f3=d.f3)
--这样。
create trigger tri_A on A for delete
as
insert into B(f1,f2,f3,f4) select f1,f2,f3,f4 from deleted a
where not exists(select 1 from B where f1=a.f1 and f2=a.f2 and f3=a.f3)
go
if exists(select * from tablea a join tableb b on a.a=b.a and a.b=b.b and a.c=b.c)
return
insert into B(f1,f2,f3,f4)
select d.f1,d.f2,d.f3,d.f4 from B right join Deleted as d on B.f1=d.f1 and B.f2=d.f2 and B.f3=d.f3
where B.f1 is null
if exists(select * from tablea a join tableb b on a.a=b.a and a.b=b.b and a.c=b.c)
return
insert into B(f1,f2,f3,f4)
select d.f1,d.f2,d.f3,d.f4 from B right join Deleted as d on B.f1=d.f1 and B.f2=d.f2 and B.f3=d.f3
where B.f1 is null
ON ta
FOR DELETE
AS
INSERT tb (f1,f2,f3,f4)
SELECT a.* FROM deleted a
INNER JOIN
(
SELECT MIN(f1) f1,f2,f3,f4
FROM deleted
GROUP BY f1,f2,f3
) b
ON a.f1=b.f1
AND a.f2=b.f2
AND a.f3=b.f3
AND a.f4=b.f4
LEFT JOIN tb c
ON b.f2=c.f2
AND b.f3=c.f3
AND b.f4=c.f4
WHERE c.f1 IS NULL
for delete
as
insert into B select * from deleted a where not exists(select * from B where B.f1=a.f1
and B.f2=a.f2 and B.f3=a.f3)
原表A,目标表B
删除表A的记录时,表A中的四个字段(f1,f2,f3,f4)保存到B表去
就是保存时不要重复保存在B表,f1,f2,f3为组合关键字*/
create trigger tg_B
on A
for delete
as
insert into B(f1,f2,f3,f4) select d.f1,d.f2,d.f3,d.f3 from deleted d
5楼的还应该改一下:(否则会插入f1,f2,f3重复记录)
CREATE TRIGGER trgDel ON TableName
FOR DELETE
AS
INSERT INTO B(f1,f2,f3,f4)
SELECT a.f1,a.f2,a.f3,a.f4
FROM (select f1,f2,f3,f4=min(f4) from deleted group by f1,f2,f3) a
LEFT JOIN B ON a.f1 =b.f1 AND a.f2=b.f2
AND a.f3=b.f3 AND a.f4=b.f4
WHERE b.f1 IS NULL
Create trige 触发器名称 on A
for delete
as
if (
select count(*) from B
where f1 = (select f1 from deleted) And
f2 = (select f2 from deleted) And
f3 = (select f3 from deleted)
)>0
return
Begin
Insert B(f1,f2,f3)
select f1,f2,f3 from deleted
end
create trigger tr_A_del on A
after delete
as
insert B(F1,F2,F3,F4)
select
F1,F2,F3,F4
from deleted d
where
not exists(select 1 from B where F1=d.F1 and F2=d.F2 and F3=d.F3)
FOR DELETE
AS
INSERT INTO B(A,B,C,D)
SELECT a.A,a.B,a.C,a.D
FROM deleted a LEFT JOIN B
ON a.A =b.A
AND a.B=b.B
AND a.C=b.C
AND a.D=b.D
WHERE b.A IS NULL and b.B is null and b.c is null
create table tri_T on A
for delete
as
insert into B select * from deleted a where not exists
(select 1 from B where f1=a.f1 and f2=a.f2 and f3=a.f3)
CREATE TRIGGER trg_name
ON A
FOR DELETE
AS
INSERT INTO B(f1,f2,f3,f4)
SELECT a.f1,a.f2,a.f3,a.f4
FROM deleted a LEFT JOIN B
ON a.f1 =b.f1
AND a.f2=B.f2
AND a.f3=B.f3
AND a.f4=B.f4
WHERE B.f1 IS NULL
create trigger tr_A_del on A
after delete
as
insert B(F1,F2,F3,F4)
select
F1,F2,F3,F4
from deleted d
where
not exists(select 1 from B where F1=d.F1 and F2=d.F2 and F3=d.F3)--其实我认为可以写一个job,每分钟(或者按满足需求时间设定)执行一次,job调用SP,SP负责检查,这样比trigger快的多,因为trigger每次操作都会触发,很影响表的性能。create procedure deleteA_insertB
as
insert B(F1,F2,F3,F4)
select
F1,F2,F3,F4
from deleted d
where
not exists(select 1 from B where F1=d.F1 and F2=d.F2 and F3=d.F3)
数据量大的话,最好不要用触发器实现
不要使用触发器