触发器问题(高手帮忙)

[MaDaHai]

create trigger Table1Update
ON Table1
after update
as
begin
DELETE FROM Table1 Where exists(select * from instered where instered.Sno = Table1.Sno)

INSERT INTO Table1 (Sno, col1, col2)
SELECT Sno, col1, col2 FROM inserted

end
go

這樣觸發的時候DELETE那個語句好象有問題,注釋了就可以
不注釋會出錯
請問怎麼回事

[tx1icenhe]

try:

DELETE FROM Table1 Where exists(select * from instered where instered.Sno = Table1.Sno)
-->

DELETE FROM Table1 Where exists(select * from instered where Sno = Table1.Sno)



不过从语句看，你的触发器还有问题，上面只是排除个语法错误而已

[MaDaHai]

sorry 寫錯了更正一下,Table1 and Table2 結構一致
create trigger Table1Update
ON Table1
after update
as
begin
DELETE FROM Table2 Where exists(select * from instered where instered.Sno = Table1.Sno)

INSERT INTO Table2 (Sno, col1, col2)
SELECT Sno, col1, col2 FROM inserted

end
go

[中国风]

create trigger Table1Update
ON Table1
after update
as
begin
INSERT INTO Table1 (Sno, col1, col2)
SELECT Sno, col1, col2 FROM inserted
DELETE FROM Table1 Where exists(select * from instered where instered.Sno = Table1.Sno)



end
把insert into放在delete前面

[MaDaHai]

放在前面insert into 的時候有主健重復的時候怎麼插入啊

[tx1icenhe]

这样改：

create trigger Table1Update
ON Table1
after update
as
begin
DELETE FROM Table2 Where exists(select * from instered i where i.Sno = Table2.Sno)

INSERT INTO Table2 (Sno, col1, col2)
SELECT Sno, col1, col2 FROM inserted

end
go

[tx1icenhe]

正常应该：

create trigger Table1Update
ON Table1
after update
as
begin
update Table2
set col1=i.col1,col2=i.col2
from Table2 ,inserted i
where i.Sno = Table2.Sno
and

INSERT INTO Table2 (Sno, col1, col2)
SELECT Sno, col1, col2
FROM inserted i
where not exists (
select 1 from Table2
where sNo=i.Sno
)

end
go

[fuxiaoyang13]

instered=》》应是inserted

[sgucxc0]

呵呵...