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 12»»

call stored procedure from trigger Expand / Collapse
Author
Message
Posted Tuesday, December 23, 2008 1:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 12:35 AM
Points: 50, Visits: 283
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
Post #624502
Posted Tuesday, December 23, 2008 1:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 3:12 AM
Points: 2,112, Visits: 5,480
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/
Post #624504
Posted Tuesday, December 23, 2008 1:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:38 AM
Points: 2,223, Visits: 3,647
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 :)






Pradeep Singh
Post #624512
Posted Tuesday, December 23, 2008 2:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:44 AM
Points: 6,731, Visits: 8,476
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #624516
Posted Tuesday, December 23, 2008 3:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 12:35 AM
Points: 50, Visits: 283
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
Post #624539
Posted Tuesday, December 23, 2008 3:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 12:35 AM
Points: 50, Visits: 283
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;
Post #624544
Posted Tuesday, December 23, 2008 3:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 12:35 AM
Points: 50, Visits: 283
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;
Post #624548
Posted Tuesday, December 23, 2008 4:47 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:38 AM
Points: 2,223, Visits: 3,647
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
Post #624579
Posted Tuesday, December 23, 2008 4:55 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:09 PM
Points: 39,968, Visits: 36,327
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 2008, MVP
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

Post #624584
Posted Tuesday, December 23, 2008 4:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:09 PM
Points: 39,968, Visits: 36,327
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 2008, MVP
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

Post #624587
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse