我的问题是存在于delete触发器,如果采用delete from table1时候,希望用触发器删除所有相关信息,同样的问题是只删除一条,现在用游标解决了,示例代码如下,希望对你有启发:
--drop trigger trg_delete_crjlb
create trigger trg_delete_crjlb on crjlb with encryption for delete as
declare @cpbs char(3),@crbh char(2)
declare del_cursor cursor for select cpbs,crbh from deleted --看看能否管用
open del_cursor
fetch del_cursor into @cpbs,@crbh
while @@fetch_status=0
begin
exec('drop table B'+@cpbs+@crbh)
fetch del_cursor into @cpbs,@crbh
end
close del_cursor
deallocate del_cursor
go
CREATE TRIGGER [NEWRCIN_INSERTED] ON [dbo].[RCIN]
FOR INSERT
AS
DECLARE @RITAG BIT,@RIENO NUMERIC
SELECT @RITAG=RITAG FROM INSERTED
IF @RITAG=1
BEGIN
DECLARE UPDATE_CURSOR CURSOR FOR SELECT RIENO FROM RCINB WHERE RIENO=(SELECT RIENO FROM INSERTED)
OPEN UPDATE_CURSOR
FETCH NEXT FROM UPDATE_CURSOR
WHILE @@fetch_status=0
BEGIN
UPDATE RCINB
SET RITAG=1 WHERE CURRENT OF UPDATE_CURSOR
FETCH NEXT FROM UPDATE_CURSOR
END
我编的是进销存系统.有一项功能为入库单审核,有入库单表和入库单商品明细表.当用户审核后,入库单表中的RITAG和明细表中的RITAG都是表示,当RITAG=1是表示此入库单或商品入库.
我设了两个触发器,一个是入库单表的,一个是明细表的.其中RIENO为入库单号.为RCIN表的主键,RCINB表的外键.
这是RCIN表中的触发器.
CREATE TRIGGER RCIN_INSERT ON dbo.RCIN
FOR INSERTED,UPDATE
AS
DECLARE @RITAG BIT
SELECT @RITAG=RITAG FROM INSERTED
IF @RITAG=1
UPDATE RCINB
SET RITAG=1
WHERE RIENO=(SELECT RIENO FROM INSERTED)
这是RCINB(入库单细表)表中的触发器.
CREATE TRIGGER RCINB_INSERT ON dbo.RCINB
FOR INSERTED,UPDATE
AS
DECLARE @RITAG BIT
SELECT @RITAG=RITAG FROM INSERTED
IF @RITAG=1 .....(执行入库操作,如使商品库存量增加等)