SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


call stored procedure from trigger


call stored procedure from trigger

Author
Message
sandy-833685
sandy-833685
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 323
Hi
i had created one stored procedure which executes by taking certain parameters and i need to create trigger on one table on insert which will execute these stored procedure by taking the parameters which will be the values which has been inserted in the table on which trigger has been created. can i know how do i proceed
Adi Cohn
Adi Cohn
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7895 Visits: 6594
You have to be more specific. What don't you know how to do? How to send a parameter to the procedure? How to write a trigger for insert? How to get the values that were inserted to the table in the trigger?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
ps.
ps.
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6951 Visits: 3668
for triggers, refer bol at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/edeced03-decd-44c3-8c74-2c02f801d3e7.htm

contains complete syntax with examples.

for Procs, refer bol at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/afe3d86d-c9ab-44e4-b74d-4e3dbd9cc58c.htm

this page also has good basic examples.

You can start writing your solution based on the understanding from these and let us know if u're stuck somewhere Smile



Pradeep Singh
ALZDBA
ALZDBA
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28759 Visits: 8986
Best practice is to keep the scope of a trigger as small as possible.

All executed by a trigger is in the same transaction as the original query (insert/update/delete) !
Meaning, if the sproc fails, the trigger fails, the insert/update/delete fails !

OR: use stored procedures to manipulate your data and then just incorporate your wanted proc in that procedure.

OR: just insert the key values and the parameters you need in another "trigger-worker-table" and have a job take care of running the sproc, starting from this trigger-worker-table.

you can even launch the job ad hoc using this method : http://www.sqlservercentral.com/scripts/Miscellaneous/31032/

Don't launch the job from within the trigger using sp_startjob, because that will need security opened up way to much !

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
sandy-833685
sandy-833685
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 323
there is table1 with coulmnA,coulmnB,coumnC,coulmnd and
there is table2 with coulmn1,coulmn2
and my procedure is

create procedure Detail
as
@cardtape VARCHAR(100),
@device varchar(10)

update table1
set coulmnB=@cardtape,coulmnC=@devid

i have to write trigger on table2 after insert the inserted value of coulmn1 and coulmn2
will the stored procedures @cardtype and @device value

that means it should be like
exec procedure Detail @cardtype,@device
where the value of @cardtype,@device will be inserted values of table2

that means whenever data is inserted in table2 and that value has to be used to process the store procedure
sandy-833685
sandy-833685
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 323
i had written these code
it give me syntax error

CREATE TRIGGER HeadCount AFTER INSERT ON table2
FOR EACH ROW BEGIN

IF(NEW. coulmn1>2 AND NEW. coulmn1 < 4 ) begin
exec HeadCountDetail(NEW.coulmn1,NEW.coulmn2);
END;
END;
sandy-833685
sandy-833685
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 323
i had written these code but it gives me syntax error
where headcountdetail is stored procedure and coulmn1 and column2 are coulmns of table2
CREATE TRIGGER HeadCount AFTER INSERT ON table2
FOR EACH ROW BEGIN

IF(NEW. coulmn1>2 AND NEW. coulmn1 < 4 ) begin
exec HeadCountDetail(NEW.coulmn1,NEW.coulmn2);
END;
END;
ps.
ps.
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6951 Visits: 3668
Modify this query as per your need.

create trigger trg_MyTrigger on myTable
After Insert
AS
Declare @cardType as varchar(100)
Declare @devid as varchar(100)
select @cardType=column1, @devid=column2 from inserted
exec HeadCountDetail @cardType, @devid



Pradeep Singh
GilaMonster
GilaMonster
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216603 Visits: 46278
santosh.lamane (12/23/2008)

CREATE TRIGGER HeadCount AFTER INSERT ON table2
FOR EACH ROW BEGIN

IF(NEW. coulmn1>2 AND NEW. coulmn1 < 4 ) begin
exec HeadCountDetail(NEW.coulmn1,NEW.coulmn2);
END;
END;


SQL doesn't have a row trigger. Triggers fire once per statement, not per row. Hence it's not recommended to have triggers call procedures that have to execute one row at a time. It's slow.

Do you absolutely have to call the proc, or can you move the logic of the proc into the trigger and use the inserted table to process all inserted rows?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216603 Visits: 46278
ps (12/23/2008)

create trigger trg_MyTrigger on myTable
After Insert
AS
Declare @cardType as varchar(100)
Declare @devid as varchar(100)
select @cardType=column1, @devid=column2 from inserted
exec HeadCountDetail @cardType, @devid



What's going to happen there if 3 rows are inserted in a single insert statement?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search