x

Which among row level and statement level triggers is effective

    Among row level and statement level trigger both runs on update/Insert/Delete 
    of records. The difference being the former runs once for each row and the later 
    runs only once, but both does same work. How to choose among these two and what 
    are the advantages and disadvantages? Also please explain if both does 
    same work what is the diff?

   DELIMITER ;;
   DROP TRIGGER IF EXISTS monthly_target_trigger;;
   CREATE TRIGGER `monthly_target_trigger` 
   AFTER INSERT on monthly_target for each row 
   BEGIN
   CALL set_weekly_plan (NEW.monthly_target_id, 

                   NEW.month_year_id);

     END ;;
    DELIMITER ;
more ▼

asked Sep 24, 2012 at 10:30 AM in Default

dharmendra1 gravatar image

dharmendra1
20 4 4 5

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You've got both sql-server-2008 and mysql tagged. Answering only for the SQL Server part of the question, there are no row-level cursors. SQL Server data manipulation (DML) triggers fire at UPDATE/INSERT/DELETE statements. You can have them fire as INSTEAD-OF triggers, meaning they substitute the function that was triggered, or as AFTER triggers which means they run after the function. Those are the basic options. For more details start here.

As to mysql. I have no idea.

more ▼

answered Sep 24, 2012 at 11:45 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

(comments are locked)
10|1200 characters needed characters left
As mentioned by Grant: Sql Server has no row level triggers. Regardless of DBMS, you'll want to use statement level triggers, not row level. If you do "something" in a trigger statement, you'll want to do that once for all the rows, not once for each row involved. Row-by-row operations will be slower than a set based operation (for the obvious reason that "once" is faster than "many times".
more ▼

answered Sep 24, 2012 at 05:39 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1850
x69

asked: Sep 24, 2012 at 10:30 AM

Seen: 2892 times

Last Updated: Sep 24, 2012 at 05:39 PM