| Author |
Topic |
|
simondeutsch
Aged Yak Warrior
547 Posts |
 Posted - 2010-02-21 : 23:21:43
|
| I am putting a stored procedure call into the INSERT and UPDATE AFTER triggers on a table that will call a CLR stored procedure. This sp sends the new or modified data over to another system. The catch is with deletions, since in an AFTER DELETE trigger the data is no longer there in the table, and the stored proc cannot access the DELETED table. Is it feasible to run an INSTEAD OF DELETE trigger on the table which should call the sp, and then rerun the original DELETE command and actually delete the row from the table?The other question is, since the CLR proc sends the data using TCP over sockets, and since it is run out of a trigger which "hangs" execution and locks the row/page/table, how many seconds should the proc be allowed to idle waiting for a response from the other system? So far, my code allows for 5 seconds, but I'm thinking perhaps it's too much. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-22 : 00:58:24
|
| "the stored proc cannot access the DELETED table"Copy the data from DELETED pseudo-table into a #TEMP table, and then call the SProc?INSTEAD OR triggers have a whole load of annoyances of their own IMHO. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-22 : 08:37:52
|
| why do you need to use CLR procedure for this? Isnt just a matter of applying delta to other system by means of export import task or linked server or OPENROWSET?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2010-02-22 : 09:56:07
|
quote:
Originally posted by Kristen
"the stored proc cannot access the DELETED table"Copy the data from DELETED pseudo-table into a #TEMP table, and then call the SProc?INSTEAD OR triggers have a whole load of annoyances of their own IMHO.
Like what?Is there any advantage to your approach, ie global temp table, rather than using an INSTEAD OF trigger with actual deletion taking place in the INSTEAD OF trigger? |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2010-02-22 : 09:57:15
|
quote:
Originally posted by visakh16
why do you need to use CLR procedure for this? Isnt just a matter of applying delta to other system by means of export import task or linked server or OPENROWSET?
The data needs to be reformatted (and involves several JOINs) into an industry standard knows as HL7, and sent via TCP/IP according to interface specs. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-22 : 10:35:32
|
| "Like what?"In the case of Insert/Update having to maintain the Instead Of trigger for all columns added to the tableVarious issues when trying to insert via a View onto a table with an Instead of Trigger in particular if the table included a Computed columnIssues related to ARITH_ABORTI'm not sure I remember them all - we got fed up with the gotchas we encountered and stopped using them.You will probably have fewer issues if you only need to use INSTEAD OF for Delete triggers."Is there any advantage to your approach, ie global temp table, rather than using an INSTEAD OF trigger with actual deletion taking place in the INSTEAD OF trigger?"I couldn't figure out how you were going to tell your SProc what rows were being effected by the trigger - if the SProc is not using the data in Inserted/Deleted?I thought CLR could reference Deleted table? (Never used one though)Perhaps if you explain that I'll have a better idea of what might be best to suggest. |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2010-02-22 : 11:08:39
|
| Ok, so most of these issues don't pertain to an INSTEAD OF DELETE trigger. However, would executing a DELETE statement within the trigger cause a loop, where the trigger would get fired again?The sproc takes 3 parameters, one of which is an ID that will be contained in the Inserted and/or Deleted tables. So far, I'm afraid I'll have to run a cursor loop in the trigger, because the sproc can only take one ID at a time and there may be a couple of rows in the Inserted table. If there are many rows, the sproc isn't executed at all.A CLR trigger can reference the Inserted and Deleted tables. An sproc cannot. The sproc I coded is called from several triggers on several tables, which is why I didn't want to code a CLR trigger and have to create several triggers within the assembly - the code is identical and then there'd be much more of it. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-22 : 11:11:37
|
quote:
Originally posted by simondeutsch
Ok, so most of these issues don't pertain to an INSTEAD OF DELETE trigger. However, would executing a DELETE statement within the trigger cause a loop, where the trigger would get fired again?The sproc takes 3 parameters, one of which is an ID that will be contained in the Inserted and/or Deleted tables. So far, I'm afraid I'll have to run a cursor loop in the trigger, because the sproc can only take one ID at a time and there may be a couple of rows in the Inserted table. If there are many rows, the sproc isn't executed at all.A CLR trigger can reference the Inserted and Deleted tables. An sproc cannot. The sproc I coded is called from several triggers on several tables, which is why I didn't want to code a CLR trigger and have to create several triggers within the assembly - the code is identical and then there'd be much more of it.
that depends on what you've set for nested triggers.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-22 : 11:19:00
|
| I think pulling the data from DELETED into a #TEMP is your best bet.SProc can reference that (instead of the actual table).Unless anyone foresees any problem?Alternatively if you are calling the SProc for a single row at a time (as you say, in your cursor loop) then can you pass all the column values it needs (and currently gets from the table direct?) |
|
|
|
|
INSTEAD OF and AFTER trigger