两个触发器在sql中的相同触发器中 [英] two triggers in same trigger in sql

问题描述

我已经为更新表创建了一个触发器，它也可以正常工作..

。

现在我想要相同的条件，对于同一个表的插入触发器，

。

有什么方法我不应该写相同的代码，而不是为插入创建单独的触发器





i有

i have create a trigger for update table, also it works properly..
.
now i want same conditions, for insert trigger for same table,
.
is there any way that i should not write same code, and not create separate trigger for insert


i have

Alter TRIGGER [dbo].[TgrLog] ON [dbo].[tblLotAssign]
FOR UPDATE
AS
insert into tblLotAssignLog values(@LotAssignNo,@SerialNo,@LotName,@tlaid,@LotID,@status,@assigndt,@ToppingDate,@RackingDate,getdate())

其中我通过虚拟tabel（插入i）获取参数，有些则通过从//另一个表中获取数据..

它的工作原理，正如我想要的那样..

但我希望插入触发器的插入查询相同

where i got parameters by virtual tabel(inserted i) and some by fetching data from // another tables..
it works, as i want..
but i want same insert query for insert trigger as

 Alter TRIGGER [dbo].[TgrLog] ON [dbo].[tblLotAssign]
FOR Insert
AS
insert into tblLotAssignLog values(@LotAssignNo,@SerialNo,@LotName,@tlaid,@LotID,@status,@assigndt,@ToppingDate,@RackingDate,getdate())

没有创建另一个触发器

without creating another trigger

推荐答案

希望这会对你有所帮助。

Hi,
Hope this will help you.

CREATE TRIGGER [dbo].[TgrLog] ON [dbo].[tblLotAssign]
FOR INSERT, UPDATE
AS
BEGIN

查看此链接了解详情：

http://stackoverflow.com/questions/741414/insert-update-trigger-how-to-determine-if-insert-or-update [ ^ ]





http://stackoverflow.com/questions/6620042/how-do-i-create-insert-update -trigger-on-sql-server-2008 [ ^ ]

您可以这样做：

You could do something like this:

CREATE TRIGGER [dbo].[TgrLog] ON [dbo].[tblLotAssign]
FOR UPDATE, INSERT
AS
    INSERT INTO
        tblLotAssignLog
    VALUES(@LotAssignNo,@SerialNo,@LotName,@tlaid,@LotID,@status,@assigndt,@ToppingDate,@RackingDate,getdate())

CREATE TRIGGER [dbo].[TgrLog] ON [dbo].[tblLotAssign]
FOR UPDATE, INSERT
AS
INSERT INTO
        tblLotAssignLog
select * from inserted

你会怎样得到



@ LotAssignNo，@ SerialNo，@ LotName，@ tlaid，@ LotID，@ status，@ assigndt，@ ToppingDate，@ RackingDat参数触发器。



您可以使用下面的标识来获取值

How would you get

@LotAssignNo,@SerialNo,@LotName,@tlaid,@LotID,@status,@assigndt,@ToppingDate,@RackingDat parameters in trigger.

You could use below to identify to get the values

SELECT * FROM INSERTED
SELECT * FROM DELETED

如果Inserted和deleted表中都有记录，那么它是一个更新，如果只存在于插入中，那么Insert and Only in deleted然后删除它。



基于此，您还可以使用标志标识来检查您在桌子上执行的操作是什么。

If record exist in both Inserted and deleted table then its a update, and if only exists in inserted then Insert and Only in deleted then its a Delete.

Based on that you can use a flag identifier also to check that what was the action performed on you table.

这篇关于两个触发器在sql中的相同触发器中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！