SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Can I call a store procedure from a trigger?
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

obedrodriguez
Starting Member

9 Posts

Posted - 01/18/2006 :  07:30:17  Show Profile
Can I Call or execute a store procedure from a trigger?
I want to pass the input parameters of the store procedure from the
trigger.

Example:
When I insert a new row in table A I want to pass the identity key of table A to table B.

Srinika
Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts

Posted - 01/18/2006 :  08:22:52  Show Profile
Can't u test it urself, by creating a test trigger in a test table, and invoking a test SP from that trigger ?
U might not need a seperate stored procedure for the operation u mentioned in ur example.
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 01/18/2006 :  08:25:28  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
Do you have Books Online? It's included free with SQL Server. If you search for Triggers on the index tab, you find this article to answer your question:

quote:

Triggers
Microsoft® SQL Server™ 2000 triggers are a special class of stored procedure defined to execute automatically when an UPDATE, INSERT, or DELETE statement is issued against a table or view. Triggers are powerful tools that sites can use to enforce their business rules automatically when data is modified. Triggers can extend the integrity checking logic of SQL Server constraints, defaults, and rules, although constraints and defaults should be used instead whenever they provide all the needed functionality.

Tables can have multiple triggers. The CREATE TRIGGER statement can be defined with the FOR UPDATE, FOR INSERT, or FOR DELETE clauses to target a trigger to a specific class of data modification actions. When FOR UPDATE is specified, the IF UPDATE (column_name) clause can be used to target a trigger to updates affecting a particular column.

Triggers can automate the processing for a company. In an inventory system, update triggers can detect when a stock level reaches a reorder point and generate an order to the supplier automatically. In a database recording the processes in a factory, triggers can e-mail or page operators when a process exceeds defined safety limits.

The following trigger generates an e-mail whenever a new title is added in the pubs database:

CREATE TRIGGER reminder
ON titles
FOR INSERT
AS
EXEC master..xp_sendmail 'MaryM',
'New title, mention in the next report to distributors.'

Triggers contain Transact-SQL statements, much the same as stored procedures. Triggers, like stored procedures, return the result set generated by any SELECT statements in the trigger. Including SELECT statements in triggers, except statements that only fill parameters, is not recommended. This is because users do not expect to see any result sets returned by an UPDATE, INSERT, or DELETE statement.

You can use the FOR clause to specify when a trigger is executed:

AFTER
The trigger executes after the statement that triggered it completes. If the statement fails with an error, such as a constraint violation or syntax error, the trigger is not executed. AFTER triggers cannot be specified for views, they can only be specified for tables. You can specify multiple AFTER triggers for each triggering action (INSERT, UPDATE, or DELETE). If you have multiple AFTER triggers for a table, you can use sp_settriggerorder to define which AFTER trigger fires first and which fires last. All other AFTER triggers besides the first and last fire in an undefined order which you cannot control.

AFTER is the default in SQL Server 2000. You could not specify AFTER or INSTEAD OF in SQL Server version 7.0 or earlier, all triggers in those versions operated as AFTER triggers.

INSTEAD OF
The trigger executes in place of the triggering action. INSTEAD OF triggers can be specified on both tables and views. You can define only one INSTEAD OF trigger for each triggering action (INSERT, UPDATE, and DELETE). INSTEAD OF triggers can be used to perform enhance integrity checks on the data values supplied in INSERT and UPDATE statements. INSTEAD OF triggers also let you specify actions that allow views, which would normally not support updates, to be updatable.


See Also

Enforcing Business Rules with Triggers

©1988-2000 Microsoft Corporation. All Rights Reserved.



MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 01/18/2006 :  08:27:25  Show Profile  Send madhivanan a Yahoo! Message
Cant you use this?

Insert into TableB(id) Select scope_identity()

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

afrika
Flowing Fount of Yak Knowledge

Nigeria
2706 Posts

Posted - 01/18/2006 :  08:41:59  Show Profile
Yes you can, very simple
exec @your_SP --list your variables to be passed


But one question. Why use a trigger? When u can call your SP directly, if you are looking to get the autoID, use any of the following

Use the scope_identity() function, it works in the scope of transcations

But you can also use SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000