简单的触发器问题。

[wwx213]

这样的触发器要怎么写？
有个表A
字段 a,b,c
表B 用来修改的记录
字段 a,b,c,operation

如果表A insert记录 要求表B相应的增加新增的记录且 operation='新增'

如果表A delete记录 要求表B相应的增加删除的记录且 operation='删除'

如果表A update记录 要求表B相应的增加update前的记录且 operation='编辑前'
同时表B相应的增加update后的记录且 operation='编辑后'

[wwx213]

表B 用来记录修改的记录

[paranoia190]

CREATE TABLE A
(A VARCHAR(250),
B VARCHAR(250),
C VARCHAR(250))


CREATE TABLE B
(A VARCHAR(250),
B VARCHAR(250),
C VARCHAR(250),
OPERATION VARCHAR(250))


CREATE TRIGGER A_INS
ON A
FOR INSERT
AS
DECLARE
@A VARCHAR(250),
@B VARCHAR(250),
@C VARCHAR(250)
SELECT @A = A,@B = B,@C = C FROM INSERTED
INSERT INTO B VALUES(@A,@B,@C,'新增')

CREATE TRIGGER A_UPD
ON A
FOR UPDATE
AS
DECLARE
@A VARCHAR(250),
@B VARCHAR(250),
@C VARCHAR(250)
SELECT @A = A,@B = B,@C = C FROM INSERTED
INSERT INTO B VALUES(@A,@B,@C,'编辑后')
SELECT @A = A,@B = B,@C = C FROM DELETED
INSERT INTO B VALUES(@A,@B,@C,'编辑前')


CREATE TRIGGER A_DEL
ON A
FOR DELETE
AS
DECLARE
@A VARCHAR(250),
@B VARCHAR(250),
@C VARCHAR(250)
SELECT @A = A,@B = B,@C = C FROM DELETED
INSERT INTO B VALUES(@A,@B,@C,'删除')

[hqhhh]

关注：

[hanlin2004]

这是最简单的触发器应用了

[gegangqiao]

楼上的只是写了SQL中的，ORACLE中的不是这样子写的

[gegangqiao]

提示一下：在ORACLE中不用临时表的，需要用到OLD和NEW变量

[fhuibo]

create trigger tri_aa on aaa for update,insert,delete as
declare @aid int,@bid int,@aname varchar(20),@aage numeric
if insert
begin
select @aid=aid from inserted
select @aname=aname from inserted
select @aage=aage from inserted
insert into bbb values(@aid,@aname,@aage,'新增')
end
create trigger tri_aab on aaa for update,insert,delete as
declare @aid int,@bid int,@aname varchar(20),@aage numeric
if delete
begin
select @aid=aid from deleted
update bbb set operation='删除' where bid=@aid
end
create trigger tri_aa on aaa for update,insert,delete as
declare @aid int,@bid int,@aname varchar(20),@aage numeric
if update
begin
select @aid=aid from deleted
select @bid=aid from inserted
select @aname=aname from inserted
select @aage=aage from inserted
update bbb set bid=@bid,bname=@aname,bage=@aage,opration='编辑后' where bid=@aid
end

[5043682]

学习!

[hqhhh]

不见意使用触发器！
在程序里控制比较好，比较安全一些！

[wwx213]

我如果一次增加、删除多条记录触发器只记录一条？

[angelior]

你说的没错

触发器是对每一条记录的MONITOR

[angelior]

至于怎写，上边有例子了

数据库有机制的，在更新之前自动有临时表