CODEWITHSUNDEEP
sql-servert-sql
Rahul
Rahul

Reputation: 77896

Can we replace a DML trigger with a stored procedure

Not sure if this has been asked before cause while typing the title text the possible duplicate given suggestion's doesn't match.

One of my colleague asked if a DML trigger functioning can be replaced totally with a stored procedure(SP). Well sounds bit weird at first but it's possible cause trigger is also a special type of SP but not explicitly callable.

I mean say for example: a AFTER INSERT Trigger named trg_insert1 defined on tbl1 which does update some data in in tbl2 like below (taken a SQL Server Example but question is not specific to any DB)

create trigger trg_insert1 
after insert on tbl1
foreach row
begin

update tbl2 set somedata = inserted.tbl1somedata
where id = inserted.tbl1id;

end

Now this trigger can be replaced with a SP like below (using transaction block);

create procedure usp_insertupdate (@name varchar(10), @data varchar(200))
as 
begin
begin try 
begin trans

insert into tbl1(name, data) values(@name, @data);

    update tbl2 set somedata = @data
    where id = scope_identity();

commit trans
end try

begin catch

if @@TRANCOUNT > 0
rollback trans
end catch
end

Which will work perfectly in almost all cases of DML trigger like after/before -> insert/delete/update. BUT I really couldn't answer/explain

what the difference then?

Is it a good practice to do so?

Is it not possible in all cases?

Am I being thinking it over complex.

Please let me know what you think.

[NOTE: Not a specific RDBMS related question though]

Upvotes: 0

Views: 987

Answers (2)

p.marino
p.marino

Reputation: 6252

I'll try to answer in a very general sense (you specified this is not targeted to a specific implementation).

First of all, a trigger is written in the same data manipulation language that you would use for a stored procedure. So in terms of capabilities Trigger and Stored Procedure are the same.

But...

a trigger is guaranteed to be invoked every time you alter the data, no matter if you do that through a stored procedure, another trigger, or by manually executing a SQL statement. In fact you can expect a trigger to always execute (for its triggering statement) unless you explicitly disable it.

A stored procedure, on the other hand it is guaranteed never to run by itself unless you explicitly run it.

This has an important consequence: triggers are better at ensuring consistency. If someone in a hurry removes a record in your live instance by typing:

Delete from tablex where uid="QWTY10311"

any bookkeeping action implemented as a trigger will be executed, while if the user forgets (or maliciously avoid) following this with 

 Execute SP_TABLEX_LOG("DELETE","QWTY10311")

your DB will just lose the data silently.

Triggers have two other important characteristics that can be duplicated with stored procedures only through extra (sometimes significantly more expensive) effort.

First of all they are executed record-by-record. So if you are deleting 1 million records the logging will be performed for each operation. Good luck calling the appropriate stored procedure with a 1 million rows cursor as a parameter, ESPECIALLY if you want to do that after a manual operation as in my example above.

Second advantage: Triggers have a special scope where they can reference pre- and post- change values for each field. So if you are incrementing a table of prices by 10% and want to log what the previous value was, and which user performed the action at what time, you will have "old-value", "new-value", "user-id" and "timestamp" in scope for any kind of operation you may want to do. Again, doing this by invoking a stored procedure means you have to save the values to pass them to the stored procedure when it runs.

So why bother with SP anyway? (this will answer, hopefully, your question about "best use case").

Stored Procedure are better when you need to create complex business logic which will be invoked by an application layer. So if you want to know, for example, how many hotel rooms are available between two given dates and with the extra requirement that pets are allowed, a trigger would not be a good idea. Especially because a trigger will not return any result to an invoking process... So anytime you need to get some result to the caller, be it a query, a calculation, or anything else that has OUTPUT parameters, a trigger is useless.

Triggers should be used to enforce consistency. If a header record should not be deleted unless it has no children in other tables, enforce this with a trigger, maybe. If you need to log whoever changes a value in a field, no matter how, use a trigger. In all other cases, use a stored procedure (keep also in mind that triggers will impact the responsiveness of any data update, just like indexes).

Upvotes: 2

Tab Alleman
Tab Alleman

Reputation: 31785

Yes stored procedures can be used to replace DML triggers in this way, and whether it is a good practice or not depends on your needs.

The main difference is that a trigger will run its code every time it is fired. In your example, if a user does an ad-hoc INSERT to tbl1, a trigger will fire and tbl2 will get updated.

A stored procedure can only be used to enforce this rule if ad-hoc INSERTs are not allowed.

Upvotes: 1

Related Questions