这个Update trigger应该怎么写?

[cBeginner]

Table Delivery
===============
DeliveryID SupplierID
1 2
2 1
3 3

Table DeliveryItem
==================
DeliveryItemID DeliveryID OrderItemID
1 1 1
2 1 2
3 2 3
4 3 4

当用户在表DeliveryItem里插入一条新纪录之后,如果在表Delivery里对应的SupplierID存在的话,那么表Delivery里的SupplierID就不能被修改.

这个trigger应该怎么写?

[奇遇]

CREATE TRIGGER tr_del ON [dbo].[delivery]
FOR DELETE
AS
if exists(select a.gno from deleted a,DeliveryItemb where a.DeliveryID=b.DeliveryID)
rollback;

[cBeginner]

gno是什么?

[cBeginner]

我改成这样写可不可以?

CREATE TRIGGER tr_del ON [dbo].[delivery]
FOR DELETE
AS
if exists(select SupplierID from deleted,DeliveryItem where Delivery.DeliveryID=DeliveryItem.DeliveryID)
begin
raiserror("can't change SupplierID",16,1)
rollback transaction
end

[奇遇]

可以的，我是拿我的两个表试了一下，gno是我表里的字段 ：）忘改了

[cBeginner]

谢谢

[cBeginner]

顺便问一下,怎么测试写好的trigger?

[cBeginner]

我觉得应该用Update,不是Delete,不知道对不对?

[奇遇]

再建一个过程
CREATE TRIGGER tr_up ON [dbo].[delivery]
FOR update
AS
if update(SupplierID)
if exists(select a.gno from deleted a,DeliveryItemb where a.DeliveryID=b.DeliveryID)
and not exists(select a.gno from inserted a,DeliveryItemb where a.DeliveryID=b.DeliveryID)
rollback;
以上都是对单行跟新、删除的情况
如果多行的问题就复杂了 ：）