Trigger Problems Part 2....
-
The Saga continues...
Can you call stored procedures from a trigger?
Can you make the trigger run async to the SP?
How do you see triggers running in the Profiler?
Scott Blakeman
"When you do something incredible, try not to look amazed" --someone
Scott Blakeman
"When you do something incredible, try not to look amazed" --someone -
quote:
Can you call stored procedures from a trigger?Yes. Think of a trigger as a specialized stored procedure.
quote:
Can you make the trigger run async to the SP?No. The trigger will wait for the stored procedure to return before continuing. If an asynchronous process is needed, look at inserting a value into a table which is checked by a job. The trigger could even schedule the job to execute. The job will then execute whatever would be needed in an asynchronous mode.
quote:
How do you see triggers running in the Profiler?Use the SP:StmtStarted and/or SP:StmtCompleted to see the trigger statements firing. For instance, if I have a trigger called ti_MyTable, when I look at the TextData column, I might see something akin to:
-- ti_MyTable
SELECT @@IDENTITYProfiler won't clearly separate trigger SQL statements from any others, except through the comment with the trigger name in TextData.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
I agree with Brian with one addition. Identities can change so scope_indentity is a better choice.
Steve Jones
-
Thanks for the help so far...
[A bit of background]
I have an app that updates some table acmem with an adnno (int) I have a trigger on this table for insert which ideally updates /inserts to a table on a remote server.
The architecture I 've created is
tri_GenesysLoginInfo
Sets parameters from the inserted row x,y
Exec's stp_GenesysLoginInfo
stp_GenesysLoginInfo
This uses parm's set by trigger to insert the data into remote table using insert statement, Has some IF statements to check target data for dupe's / exsisting data.
I have errorlogging through this statement which Exec's stp_errorhandler
stp_errorhandler
This gets @@error passed to it ,ProcID (ID's I have assigned to my proc's),adINFO (any other info I want to log)
Now whats happening is...
The trace shows the trigger firing and assigning the variables then calling
stp_GenesysLoginInfo. A trace on the genesysLOGIN shows the login being opened then nothing else happens...
And it also fails to insert the original data into the source table (acmem)
Questions
1. Is there any limitations on what you can do with a trigger in context of what I am doing?
2. Is there a way to trap errors and allow the trigger to complete(thus allowing the original inset to occur ? [acmem])
3. Any suggestion on how I should be doing this?
Thanks for your time...
Scott Blakeman
"When you do something incredible, try not to look amazed" --someone
Scott Blakeman
"When you do something incredible, try not to look amazed" --someone -
Have you traed on the remote server as well to see what it is doing. Since the data is all one transaction I believe it will fail to commit no matter what to the original data table. You may want to create a table local to input the changes and then have a job to run every so often (or create it disabled then turned on by the trigger using sp_start_job after the insert to avoid unneccessary running). This way you should be able to write the data locally and your first table should insert then your data is written by the job to the remote server.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply