Instead of trigger

Question

Instead of trigger

Keith DuAime

Ten Centuries

Points: 1129
More actions
June 11, 2009 at 1:15 pm

#384760

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 😉

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

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

FreeHansje SSChampion Points: 11751 More actions · Answer 1

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 Ten Centuries Points: 1129 More actions · Answer 2

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. One Orange Chip Points: 29422 More actions · Answer 3

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 Ten Centuries Points: 1129 More actions · Answer 4

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. One Orange Chip Points: 29422 More actions · Answer 5

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