Instead of trigger

  • 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 😉

  • 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

  • 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.

  • 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

  • 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.

  • 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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply