two triggers in same trigger in sql

Question

0.00/5 (No votes)

See more:

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())

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

Posted 4-Nov-14 21:25pm

Madhuri Gamane

Add a Solution

3 solutions

Solution 2

You could do something like this:

SQL

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())

Posted 4-Nov-14 21:38pm

Manas Bhardwaj

Solution 3

SQL

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

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

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.

Posted 4-Nov-14 21:51pm

Shweta N Mishra

Updated 4-Nov-14 22:06pm

v2

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

syed shanu · Accepted Answer · 2014-11-04T21:32:00

Solution 1

Hi,
Hope this will help you.

SQL

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

Check this links for details :
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[^]

Posted 4-Nov-14 21:32pm

syed shanu

Comments

Manas Bhardwaj 5-Nov-14 3:38am

ditto +5!

Madhuri Gamane 5-Nov-14 3:38am

Thank you very much, it works... :-))

two triggers in same trigger in sql

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

two triggers in same trigger in sql

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us