Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Trigger on condition Expand / Collapse
Author
Message
Posted Thursday, May 23, 2013 2:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 227, Visits: 723
Hi Team,

i need a trigger based on condition.



CREATE TRIGGER [dbo].[Trigger_Base_line] on [dbo].[Base_Tab]
AFTER INSERT,DELETE,UPDATE


here i want a condition logic in trigger,

if a record is inserted into base_tab
then
Execute Stored_procedure1


if a record is deleted from base_tab
then
Execute Stored_procedure2


if a record is updated in base_tab
then
Execute Stored_procedure3


please help me...
Post #1455870
Posted Thursday, May 23, 2013 2:32 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:26 AM
Points: 494, Visits: 426
Instead of triggers you can use OUTPUT Parameter, Like Below.
--- For Insert ---
INSERT INTO TableName
OUTPUT INSERTED.* INTO ToTableName
VALUES
---
--- For Update ---
UPDATE TableName SET ColumnToupdate
OUTPUT deleted.* INTO ToUpdTableName
---
--- For Delete ---
DELETE FROM TableName
OUTPUT deleted.* INTO ToDelTableName
WHERE
---

I suggested using OUTPUT because your trigger is ALTER INSERT, UPDATE, DELETE it mince you want to update this after the data inserted/updated/deleted.
Post #1455873
Posted Thursday, May 23, 2013 2:36 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 4, 2014 7:40 AM
Points: 3,545, Visits: 2,652
Why not try three different triggers rather than using one ?

If you insist on making just one trigger based on all events, here is what you can do inside the trigger:

IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) 
BEGIN
EXEC PROC_FOR_UPDATE
END
IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted)
BEGIN
EXEC PROC_FOR_INSERT
END
IF NOT EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
BEGIN
EXEC PROC_FOR_DELETE
END

Post #1455876
Posted Thursday, May 23, 2013 2:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 227, Visits: 723
Thank u,

my requirement is when ever insert happens on main table then i want to execute Stored proc1,
if delete happens on main table then execute stored proc2,
if update then stored proc3,

how to know what operation (insert/delete/update) happens on main table.
Post #1455877
Posted Thursday, May 23, 2013 2:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 227, Visits: 723
Thank U sqlnaive

Hope it works.

Great...!
Post #1455880
Posted Thursday, May 23, 2013 2:42 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:26 AM
Points: 494, Visits: 426
sqlnaive (5/23/2013)
Why not try three different triggers rather than using one ?

If you insist on making just one trigger based on all events, here is what you can do inside the trigger:

IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) 
BEGIN
EXEC PROC_FOR_UPDATE
END
IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted)
BEGIN
EXEC PROC_FOR_INSERT
END
IF NOT EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
BEGIN
EXEC PROC_FOR_DELETE
END


Insert Happens mince do you mean by AFTER INSERT OR INSTEAD OF INSERT, if it is after insert you can create three seprate triggers or suggsted in previous post or use output parameter as I described , If it is INSTEAD OF INSERT the mail insert will not fire in its place you can update other table as required.
Post #1455885
Posted Thursday, May 23, 2013 4:38 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 4, 2014 7:40 AM
Points: 3,545, Visits: 2,652
Bhaskar.Shetty (5/23/2013)
[quote]Insert Happens mince do you mean by AFTER INSERT OR INSTEAD OF INSERT, if it is after insert you can create three seprate triggers or suggsted in previous post or use output parameter as I described , If it is INSTEAD OF INSERT the mail insert will not fire in its place you can update other table as required.


Just play it simple. It's upto your choice what you want to achieve. I just gave two options.

In between, would love to see how it worked.
Post #1455933
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse