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

Instead of trigger Expand / Collapse
Author
Message
Posted Thursday, June 11, 2009 1:15 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 11:14 AM
Points: 140, Visits: 312
Interesting problem here. There is an application that insert/updates/deletes from a table but is very inconsistent and tools to run in order to fix it. So I'd rather use triggers on the transactional tables in case a connection is lost, canceled etc... So conceptually the application will be inserting to a table and the information should be added to the summary table, however the application may also attempt to update the table. Currently my thought was create an instead of trigger on the summary table and an after trigger on the transaction table, where in the transaction it creates a temp table named #PostRecord and in the instead of trigger there is code to check the object_id('tempdb..#PostRecord ). Is this the best method? I can give a sample block of code if I'm not making sense but should be straight forward for some of our experts
Post #733286
Posted Friday, June 12, 2009 7:08 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 6, 2012 8:30 AM
Points: 879, Visits: 810
Maybe I'm not an expert, but I am unsure what it is you wish to accomplish. I do not want a block of code, just the bussiness logic. As I understand it you have TableA, which can have records inserted. When a record is inserted in TableA another record should be inserted in TableB.
Now comes the tricky part: do you wish to do an update to TableB if TableA is updated, or the other way around, or something else? You talk of INSTEAD; are you suggesting TableA might be updated BEFORE the value to be updated is actually committed?
Care to explain?



Greetz,
Hans Brouwer
Post #733778
Posted Monday, June 15, 2009 6:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 11:14 AM
Points: 140, Visits: 312
I have TableA which is transactional. TableB is basically a roll-up(sum of data and additional data) of TableA. Currently the application is inserting/updating both TableA and TableB. It is beyond my control and cannot stop the application from updating TableB however that's what I want to do. So the application is going to INSERT TableA, then UPDATE TableB but I want to make a trigger on TableA to update TableB correctly and when the application issues UPDATE TableB, I want it to do nothing.
Post #734952
Posted Monday, June 15, 2009 8:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:38 AM
Points: 2,242, Visits: 3,647
If your application is using a distinct user id, the u can write an INSTEAD OF trigger on table b and check if the user_id is same as application's. if this condition satisfies, insert/update can be prevented.

create trigger trg_stock on test instead of insert, update
as
if SYSTEM_USER ='sa'
print 'not inserted/updated'







Pradeep Singh
Post #735028
Posted Monday, June 15, 2009 8:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 11:14 AM
Points: 140, Visits: 312
The application is using a particular user but is also using that user for TableA ins/upd so TableB wouldn't be updated by the trigger on TableA.
Post #735046
Posted Monday, June 15, 2009 8:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:38 AM
Points: 2,242, Visits: 3,647
the trigger i wrote will prevent all inserts/updates on TableB even if the insert/update is coming directly from the application or as a result of trigger on TableA, if it is initiated by a particular user.

U just wanted the application not to write to TableB, this does just what u need i guess








Pradeep Singh
Post #735060
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse