现在我有一个文章表,我现在希望在文章状态改变的时候把当前文章ID,状态存到一个临时表Temp中
请问怎么编写这个触发器?例如:
文章状态有5种 分别用1,2,3,4,5表示,我只想监视文章状态变成4,5的情况
当文章状态变成5的时候 将当前文章ID,文章状态,和文章表标示Article存入临时表Temp_table中create table Artile
(id int, name varchar(60),state int)
go
insert into Artile values(1,'AA',1)
insert into Artile values(2,'BB',2)
insert into Artile values(3,'CC',3)
insert into Artile values(4,'DD',4)
insert into Artile values(5,'EE',5)
create table Temp_table
(id int, state int, updateTable varchar(60))
go比如我修改了AA文章 将状态改成4 就在Temp_table 表中添加记录
-------------------------
id state updateTable
--------------------------
1 4 Article如果再将AA文章状态改成5 就修改当前Temp_table 表中记录
请问怎么编写这个触发器?例如:
文章状态有5种 分别用1,2,3,4,5表示,我只想监视文章状态变成4,5的情况
当文章状态变成5的时候 将当前文章ID,文章状态,和文章表标示Article存入临时表Temp_table中create table Artile
(id int, name varchar(60),state int)
go
insert into Artile values(1,'AA',1)
insert into Artile values(2,'BB',2)
insert into Artile values(3,'CC',3)
insert into Artile values(4,'DD',4)
insert into Artile values(5,'EE',5)
create table Temp_table
(id int, state int, updateTable varchar(60))
go比如我修改了AA文章 将状态改成4 就在Temp_table 表中添加记录
-------------------------
id state updateTable
--------------------------
1 4 Article如果再将AA文章状态改成5 就修改当前Temp_table 表中记录
on Artile
for update
as
declare @ int
declare @id int
select @=state from inserted
if(@=4 or @=5)
begin
if(exists(select count(*) from temp_table))
select @id=max(id)+1 from temp_table
else
set @id=1
insert into Temp_table values(@id,@,'Artile')
end
献一个
as
if update(state)
begin
insert into Temp_table(id,state,updatetable)
select id,state,'Artile' from inserted where state=4
update Temp_table set state=a.state from inserted a where a.state=5
end
create trigger t_u_temp on Artile
for update
as
begin
if update([state])
if exists(select 1 from Temp_table where id in (select id from inserted where [state] in (4,5)))
update a set [state] = b.[state]
from Temp_table a inner join inserted b on a.id = b.id
where b.[state] in (4,5)
else
insert into Temp_table(id,[state],updateTable)
select id,[state],'Artile'
from inserted
where [state] in (4,5)
end
(id int, name varchar(60),state int)
go
insert into Article values(1,'AA',1)
insert into Article values(2,'BB',2)
insert into Article values(3,'CC',3)
insert into Article values(4,'DD',4)
insert into Article values(5,'EE',5)
create table Temp_table
(id int, state int, updateTable varchar(60))
gocreate trigger tri_state on Article
for update
as
if update(state)
begin
update Temp_table
set state=i.state
from inserted i, deleted d
where i.id=d.id and i.id=Temp_table.id insert Temp_table
select i.id,i.state,'Article'
from inserted i join deleted d
on i.id=d.id and (i.state=4 or i.state=5)
and i.id not in(select id from Temp_table)
end
go--更新为4
update article set state=4 where id=1select * from Temp_table
/*
1 4 Article
*/--更新为5
update article set state=5 where id=1select * from Temp_table
/*
1 5 Article
*/
create trigger tri_state on Article
for update
as
if update(state)
begin
update Temp_table
set state=i.state
from inserted i, deleted d
where i.id=d.id and i.id=Temp_table.id and (i.state=4 or i.state=5) insert Temp_table
select i.id,i.state,'Article'
from inserted i join deleted d
on i.id=d.id and (i.state=4 or i.state=5)
and i.id not in(select id from Temp_table)
end
go
IF OBJECT_ID('ARTILE')IS NOT NULL DROP TABLE ARTILE
GO
create table Artile
(id int, name varchar(60),state int)
go
insert into Artile values(1,'AA',1)
insert into Artile values(2,'BB',2)
insert into Artile values(3,'CC',3)
insert into Artile values(4,'DD',4)
insert into Artile values(5,'EE',5)
IF OBJECT_ID('TEMP_TABLE')IS NOT NULL DROP TABLE TEMP_TABLE
GO
create table Temp_table
(id int, state int, updateTable varchar(60))
go
IF OBJECT_ID('TRI_UPDATE')IS NOT NULL DROP TRIGGER TRI_UPDATE
GO
CREATE TRIGGER TRI_UPDATE ON ARTILE
FOR UPDATE
AS
IF NOT EXISTS(SELECT 1 FROM TEMP_TABLE T, INSERTED I WHERE T.ID=I.ID )
INSERT TEMP_TABLE SELECT ID,STATE,'ARTILE' FROM INSERTED WHERE STATE IN(4,5)
ELSE
UPDATE T SET STATE=I.STATE FROM INSERTED I INNER JOIN TEMP_TABLE T ON T.ID=I.ID
GO
UPDATE ARTILE SET STATE=4 WHERE NAME='AA'
SELECT * FROM TEMP_TABLE
UPDATE ARTILE SET STATE=5 WHERE NAME='AA'
SELECT * FROM TEMP_TABLE
/*id state updateTable
----------- ----------- ------------------------------------------------------------
1 4 ARTILEid state updateTable
----------- ----------- ------------------------------------------------------------
1 5 ARTILE
*/
go
insert into Artile values(1,'AA',1)
insert into Artile values(2,'BB',2)
insert into Artile values(3,'CC',3)
insert into Artile values(4,'DD',4)
insert into Artile values(5,'EE',5)
create table Temp_table (id int, state int, updateTable varchar(60))
gocreate trigger my_trig on Artile for insert ,update ,delete
as
if not exists(select 1 from inserted where state in (4,5))
delete temp_table from deleted t where temp_table.id = t.id
else if not exists(select 1 from deleted)
insert into Temp_table select id , state , 'Artile' from inserted
else
update temp_table set state = t.state from inserted t where temp_table.id = t.id
goinsert into artile values(6 , 'ff' , 5)
select * from Temp_table
/*
id state updateTable
----------- ----------- ------------------------------------------------------------
6 5 Artile(所影响的行数为 1 行)
*/update artile set state = 4 where id = 6
select * from Temp_table
/*
id state updateTable
----------- ----------- ------------------------------------------------------------
6 4 Artile(所影响的行数为 1 行)
*/delete artile where id = 6
select * from Temp_table
/*
id state updateTable
----------- ----------- ------------------------------------------------------------ (所影响的行数为 0 行)
*/
drop table Artile , Temp_table
on Artile
for update
as
--判断修改之后的值是否为4,5
if exists(select * from inserted where score='4' or score='5' )
begin
if(exists(select 1 from temp_table inner join inserted on temp_table.id = inserted.id ))
Update br
Set br.state =i.state
From temp_table br , Deleted d ,Inserted i
Where br.id=d.id
else
insert into temp_table
select id,score,'Artile' from inserted
end
能帮我看看我这个触发器吗当我第一次修改state值的时候 temp_table表确实加入了数据
如:update Artile
set state=4 where id = 1temp_table结果是:
-------------------------
id state updateTable
--------------------------
1 4 Article
但是我第二次修改同一条记录的时候
update Artile
set state=5 where id = 1
temp_table结果变成了2条:
-------------------------
id state updateTable
--------------------------
1 5 Article
1 5 Article
这是怎么回事的?
FOR UPDATE
AS
IF NOT EXISTS(SELECT 1 FROM TEMP_TABLE T, INSERTED I WHERE T.ID=I.ID )
INSERT TEMP_TABLE SELECT ID,STATE,'ARTILE' FROM INSERTED WHERE STATE IN(4,5)
ELSE
UPDATE T SET STATE=I.STATE FROM INSERTED I INNER JOIN TEMP_TABLE T ON T.ID=I.ID
GO
把数据清理一下,把tr1重建一遍,再测试。