CREATE TRIGGER room_with_call
ON dbo.room
INSTEAD OF UPDATE
AS
BEGIN
If update(roomstate_id)
Begin
DECLARE @o_roomstate_id int,
@n_roomstate_id int,
@id int;
SELECT @id = id FROM INSERTED;
SELECT @n_roomstate_id = roomstate_id FROM INSERTED;
SELECT @o_roomstate_id = (SELECT roomstate_id FROM dbo.room WHERE id = @id)
IF @o_roomstate_id = 2
Begin
DELETE dbo.call WHERE room_id = @id;
END
UPDATE dbo.room SET roomstate_id = @n_roomstate_id WHERE id = @id;
End
END
ON dbo.room
INSTEAD OF UPDATE
AS
BEGIN
If update(roomstate_id)
Begin
DECLARE @o_roomstate_id int,
@n_roomstate_id int,
@id int;
SELECT @id = id FROM INSERTED;
SELECT @n_roomstate_id = roomstate_id FROM INSERTED;
SELECT @o_roomstate_id = (SELECT roomstate_id FROM dbo.room WHERE id = @id)
IF @o_roomstate_id = 2
Begin
DELETE dbo.call WHERE room_id = @id;
END
UPDATE dbo.room SET roomstate_id = @n_roomstate_id WHERE id = @id;
End
END
解决方案 »
- 很急提问,请大侠们帮忙
- [求助]一个简单存储过程,总出错,请高手指点!在线等~
- 数据库复制的问题
- MSSQL2000 and MSSQL2005 Express哪个好些
- 急求,有关sql加asp问题。
- 改ttserver源码实现主从模式
- how to write
- 寻求客户端不安装SQLSERVER而进行DTS调用的解决办法
- EXEC master..xp_cmdshell ..怎么执行呀?
- 我想把一个表里的一个记录复制一个,除了identity 列之外的所有列,sql语句怎么写?how to
- 谁能告诉我当sql server2000中的一个表中存有2千万条的数据的话,检索一次需要多长时间?
- 在触发器中如何知道它的引发动作?
CREATE TRIGGER room_with_call
ON dbo.room
For UPDATE
AS
If update(roomstate_id)
delete dbo.call where room_id in (select id from deleted where roomstate_id = 2)
更改room表其他字段的时候,触发器还是会把update操作cancel掉...郁闷