SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Instead of trigger


Instead of trigger

Author
Message
Keith DuAime
Keith DuAime
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 344
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 ;-)
FreeHansje
FreeHansje
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1573 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
Keith DuAime
Keith DuAime
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 344
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.
ps.
ps.
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4129 Visits: 3668
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
Keith DuAime
Keith DuAime
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 344
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.
ps.
ps.
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4129 Visits: 3668
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search