May 23, 2013 at 2:20 am
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...
May 23, 2013 at 2:32 am
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.
May 23, 2013 at 2:36 am
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
May 23, 2013 at 2:37 am
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.
May 23, 2013 at 2:39 am
Thank U sqlnaive
Hope it works.
Great...!
May 23, 2013 at 2:42 am
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.
May 23, 2013 at 4:38 am
Bhaskar.Shetty (5/23/2013)
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply