SQL触发器设计与应用-优快云博客

Q:我有表student(stid,sex,adclass)其中stid是主键wish(stid,proid1)其中stid为student中的stid的foreign key,我想为student 设计一个触发器使：在student表中当前插入的行也被插入到wish表中，请问这样怎么做。

A:CREATE   TRIGGER   student_Insert   ON   dbo.student
FOR   INSERT   Not   For   Replication
AS
    Insert   into   wish   (stid)
    Select   stid   From   Inserted

=============================
表Table，其中有一个日期的字段date1（varchar2类型，格式如：2004年09月30日）。当给这个表增加一条记录的时候，删除全部超出距离当前加入的date1字段的值10天的所有记录。

首先必须要把date1转化为DATE型。做个Insert触发器，当触发的时候进行判断即可

CREATE OR REPLACE TRIGGER DELETE_OVERDUE
AFTER INSERT ON Table
BEGIN
DELETE FROM Table
WHERE TO_DATE(date1,'yyyy-mm-dd') < TURN(date1) - 10;
END;

=====================================
用事务好点，要成功就都会成功，只要有一个失败，就都会失败
这样就能保证数据的同步
create   proc   sp_test(@value1   varchar(50),@value2   varchar(50),@value3   varchar(50)...)
as
begin   transaction
insert   tb1(col1,col2,col3...)   select   @value1,@value2,@value3...
if   @@error <> 0
begin
    Rollback
end
insert   tb2(col1,col2,col3...)   select   @value1,@value2,@value3...
if   @@error <> 0
begin
    Rollback
end
commit
===================================
sql server 2000 触发器,表同步更新的问题
2有三个表，A ，B，C
3A、B表中含有: A1,B1,C1 三个字段,
4C 表中存放A、B表中的A1、B1、C1 的集合,
5字段类型都为nvarchar(10),
6当表A的数据被更新、删除、插入后要反映到C表。
7当表B的数据被更新、删除、插入后要反映到C表。
8假定A,B表中在a1,b1,c1上有唯一索引
9
10
11      这个问题如果纯属从理论来说，是很容易解决的，因为从要求可知，实质上C表存放的数据即为A、B表的并集。可以在A、B表上创建相同的trigger，一旦A、B表上有变化，比如插入、删除或更新时，即清空C表数据，然后把A、B表的数据union后插入C表中即可实现目的:)呵呵呵。。。
12
13      下面的trigger的实现原理是：
14
15       当A表插入数据时，检查C表中是否有A表将要插入的数据，如果无，则将这行数据插入到C表中，反之，则不需要操作。
16
17       当A表update时, 检查B表中是否有更新前这行数据，如果有，则C表中应该保留这行数据且把A表中更新后的数据也插入到C表中去。如果B表中没有A表更新前的这行数据且C表中没有A表更新后的这行数据，则需要用A表更新后的数据来更新C表中与A表更新前这行数据相同的数据；如果B表中没有A表更新的的这行数据且C表中有A表更新后的这行数据，则需要从C表中删除跟A表更新前相同的那行数据（因为更新A表后，A表和B表都没有A表更新前的那行数据了，则这行数据显然在C表中不应该再存在了）。
18
19       当A表中删除时，检查B表是否还存在A表要删除的这行数据，如果有，则不能删除C表中与A表要删除的数据相同的行。反之，则执行删除操作。
20
21
22    B表中的trigger跟A表中的原理相同。
23
24
25CREATE TRIGGER SYNC_C_BY_A
26ON A
27AFTER INSERT,UPDATE,DELETE
28AS
29Declare @Dml            TinyInt --1:Insert 2:Update 3:Delete
30Declare @RowsD          Int
31Declare @RowsI          Int
32Declare @A1_D           nvarchar(10)
33Declare @B1_D           Nvarchar(10)
34Declare @C1_D           Nvarchar(10)
35--确定是哪一种dml操作
36Select @RowsD=Count(*) From Deleted
37Select @RowsI=Count(*) From Inserted
38If @RowsD=0 And @RowsI=0
39    Goto Exit_
40If @RowsD=0 And @RowsI>0
41    Set @Dml=1
42Else
43   If @RowsD>0 And @RowsI>0
44       Set @Dml=2
45   Else
46       If @RowsD>0 And @RowsI=0
47           Set @Dml=3
48IF @DML=1
49   BEGIN
50       --检查c表中是否已经有A表中新插入的数据行,如果没有,则也插入
51       IF NOT EXISTS(SELECT TOP 1 1 FROM c,inserted i where c.a1=i.a1 and c.b1=i.b1 and c.c1=i.c1)
52          insert into c select * from inserted
53   END
54IF @DML=2
55   BEGIN
56       --检查B表中是否有A表中更新前的这行数据,如果有,则不需要更新C表中的数据,而是要把A表中更新后的这行数据插入到C表中
57       IF NOT EXISTS(SELECT TOP 1 1 FROM B,DELETED d where b.a1=d.a1 and b.b1=d.b1 and b.c1=d.c1)
58         BEGIN
59             --如果C表中不存在A表更新后的这行数据,则更新C表中跟A表更新前那行数据相同的数据
60             IF NOT EXISTS(SELECT TOP 1 1 FROM C,INSERTED I WHERE C.A1=I.A1 AND C.B1=I.B1 AND C.C1=I.C1)
61                 BEGIN
62                     UPDATE C SET A1=I.A1,B1=I.B1,C1=I.C1 FROM C,INSERTED I,DELETED D WHERE C.A1=D.A1 AND C.B1=D.B1 AND C.C1=D.C1
63                 END
64             --如果C表中存在A表更新后的这行数据,则需要删除C表中跟A表更新前相同的那行数据
65             ELSE
66                 BEGIN
67                     SELECT @A1_D=A1,@B1_D=B1,@C1_D=C1 FROM DELETED
68                     DELETE FROM C WHERE @A1_D=A1 AND @B1_D=B1 AND @C1_D=C1
69                 END
70         END
71       ELSE
72          insert into c select * from inserted i where not exists(select 1 from c where i.a1=c.a1 and i.b1=c.b1 and i.c1=c.c1)
73   END
74IF @DML=3
75   BEGIN
76       --如果B表中不存在A表要删除的这行数据,则需要从C表中删除这行数据
77       IF not exists(select top 1 1 from b,deleted d where b.a1=d.a1 and b.b1=d.b1 and b.c1=d.c1)
78       DELETE FROM C WHERE EXISTS(SELECT 1 FROM deleted d where c.a1=d.a1 and c.b1=d.b1 and c.c1=d.c1)
79   END
80EXIT_:
81
82CREATE TRIGGER SYNC_C_BY_B
83ON B
84AFTER INSERT,UPDATE,DELETE
85AS
86Declare @Dml            TinyInt --1:Insert 2:Update 3:Delete
87Declare @RowsD          Int
88Declare @RowsI          Int
89Declare @A1_D           nvarchar(10)
90Declare @B1_D           Nvarchar(10)
91Declare @C1_D           Nvarchar(10)
92--确定是哪一种dml操作
93Select @RowsD=Count(*) From Deleted
94Select @RowsI=Count(*) From Inserted
95If @RowsD=0 And @RowsI=0
96    Goto Exit_
97If @RowsD=0 And @RowsI>0
98    Set @Dml=1
99Else
100   If @RowsD>0 And @RowsI>0
101       Set @Dml=2
102   Else
103       If @RowsD>0 And @RowsI=0
104           Set @Dml=3
105IF @DML=1
106   BEGIN
107       --检查c表中是否已经有B表中新插入的数据行,如果没有,则也插入
108       IF NOT EXISTS(SELECT TOP 1 1 FROM c,inserted i where c.a1=i.a1 and c.b1=i.b1 and c.c1=i.c1)
109          insert into c select * from inserted
110   END
111IF @DML=2
112   BEGIN
113       --检查B表中是否有A表中更新前的这行数据,如果有,则不需要更新C表中的数据,而是要把A表中更新后的这行数据插入到C表中
114       IF NOT EXISTS(SELECT TOP 1 1 FROM A,DELETED d where a.a1=d.a1 and a.b1=d.b1 and a.c1=d.c1)
115         BEGIN
116             --如果C表中不存在B表更新后的这行数据,则更新C表中跟b表更新前那行数据相同的数据
117            IF NOT EXISTS(SELECT TOP 1 1 FROM C,INSERTED I WHERE C.A1=I.A1 AND C.B1=I.B1 AND C.C1=I.C1)
118                BEGIN
119                    UPDATE C SET A1=I.A1,B1=I.B1,C1=I.C1 FROM C,INSERTED I,DELETED D WHERE C.A1=D.A1 AND C.B1=D.B1 AND C.C1=D.C1
120                END
121             --如果C表中存在更新B表后的这行数据,则需要删除C表中跟B表更新前相同的那行数据
122            ELSE
123                BEGIN
124                    SELECT @A1_D=A1,@B1_D=B1,@C1_D=C1 FROM DELETED
125                    DELETE FROM C WHERE @A1_D=A1 AND @B1_D=B1 AND @C1_D=C1
126                End
127
128         END
129       ELSE
130          insert into c select * from inserted i where not exists(select 1 from c where i.a1=c.a1 and i.b1=c.b1 and i.c1=c.c1)
131   END
132IF @DML=3
133   BEGIN
134       --如果A表中不存在B表要删除的这行数据,则需要从C表中删除这行数据
135       if not exists(select top 1 1 from a,deleted d where a.a1=d.a1 and a.b1=d.b1 and a.c1=d.c1)
136       DELETE FROM C WHERE EXISTS(SELECT 1 FROM deleted d where c.a1=d.a1 and c.b1=d.b1 and c.c1=d.c1)
137   END
138EXIT_:

检举|2010-08-29 23:06 wangzhiqing999 |当前分类： 28 级排名：1

Oracle 触发器有2种
一种语句级别的
一种行级的 FOR EACH ROW

有 BEFORE 有 AFTER
取得 操作数据 的是通过  :old  :new
BEFORE 的时候， 可以通过 :new.xxx 来修改数据。

对于 INSERT OR UPDATE OR DELETE 的
通过 INSERTING UPDATING DELETING 进行判断。

通过 OF 字段 ON 表 实现只针对特定列的触发。

-----------------------------------------------------
SQL Server

触发器只有 语句级别的
只有 AFTER
取得 操作数据，通过 inserted deleted

编写触发器的时候，要充分考虑 一次更新一条，与一次更新多条的问题。
避免简单的 SELECT @NewName = name FROM inserted; 代码操作。

对于 FOR INSERT,UPDATE,DELETE 的
通过 查询 inserted 与 deleted 的记录 来判断

通过COLUMNS_UPDATED 实现在触发以后，判断哪些列被更新了。

============================================================

我是这么怀疑的。

楼主可能是按照 Oracle 的 FOR EACH ROW 的思路，来写那个触发器的。


也就是，假如
UPDATE table_1 SET coulmn_1 = 10 WHERE column_2 = 5;
会更新3条记录。

那么这个 SQL 语句，在 Oracle 的 FOR EACH ROW 的触发中，会被触发3次。


但是在 SQL Server 中， 触发器是 语句级别的。
也就是说：
UPDATE table_1 SET coulmn_1 = 10 WHERE column_2 = 5;
会更新3条记录。
但是触发器，只执行了一次。

如果你简单的，就

SELECT ... FROM inserted 
SELECT ... FROM deleted 

的话，那么你最后检查的时候，就会以为 “只触发了一条数据”。


楼主 可以修改一下那个 存储过程。

首先 SELECT COUNT(1) FROM inserted 

来判断，总共修改了多少条 记录， 然后根据需要 做循环处理。