another way instead trigger

Question

hi

how do I get the type of automatic actions (similar to the once done by triggers) using stored procedures instead, or any other way to this, because i need when i (insert or update or delete) a record in tableA ,or tableB i need to do the same operation in table C,

i don't want to use the trigger because some Disadvantages such as :

• It is easy to view table relationships , constraints, indexes, stored procedure in database but triggers are difficult to view.
• Triggers execute invisible to client-application application. They are not visible or can be traced in debugging code.
• It is hard to follow their logic as it they can be fired before or after the database insert/update happens.
• It is easy to forget about triggers and if there is no documentation it will be difficult to figure out for new developers for their existence.
• Triggers run every time when the database fields are updated and it is overhead on system. It makes system run slower

So, i'm just thinking... is it possible to get the similar result with out trigger?? thanks

Answer 1

If you make sure all your code that inserts into tableA or tableB goes through a single, common codepath, then you'd do the insert into tableC at the same time.

For example, you could implement a stored procedure which everything must call in order to insert a record into tableA - as part of this sproc, you'd just include the operation to also insert into tableC.

This requires, that all code goes through that common point. If not, you have to make sure everywhere then whenever an insert is done in tableA, it repeats it into tableC. This can be a lot more work.

This is why triggers are handy, as it makes it automatic. And you don't have to ensure all code paths do the appropriate call (this can be very tricky in an existing/legacy codebase)

Answer 2

Not directly answering your question but addressing some of the things you mentioned:

It is easy to view table relationships , constraints, indexes, stored procedure in database but triggers are difficult to view.

Triggers are just as easy to view as stored procedures.

Triggers execute invisible to client-application application. They are not visible or can be traced in debugging code.

True. But in many instances, it's not trivial to step into a stored procedure as well.

It is hard to follow their logic as it they can be fired before or after the database insert/update happens.

Untrue. Triggers fire exactly at the time they are defined to fire, BEFORE or INSTEAD OF.

It is easy to forget about triggers and if there is no documentation it will be difficult to figure out for new developers for their existence.

Untrue if you are using version control.

Triggers run every time when the database fields are updated and it is overhead on system. It makes system run slower

A poorly written trigger is just as likely to perform badly as poorly written code. (But I'm not advocating their overuse. For instance, if an action can be satisfied with a constraint, use it over a trigger.)

So all that said, a trigger is still a good option. If you really must do this in the application layer, then do it there.

Answer 3

You'll need to revoke DML permissions on A and B from users and grant the permission only to execute the stored procedure which would handle inserts to C.

Answer 4

thanks all for your prompt response,,,

i understand that using trigger is a good option to some point but i was thinking ,what if :

I creat a function to (insert/update/delete) in tableC and this function could be called from any stored procedure that would (insert/update/delete) tableA and tableB. This would allow me to manage one function if a change is needed instead of modifying the code in every stored procedure. In addition, I can use a transaction in every stored procedure to keep data in all tables consistent .