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

avatar image

dharmendra1
20 4 4 7

(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

avatar image

Grant Fritchey ♦♦
137k 20 43 81

(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

avatar image

Magnus Ahlkvist
21.5k 19 39 42

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

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:

x2091
x104

asked: Sep 24, 2012 at 10:30 AM

Seen: 3455 times

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

Copyright 2016 Redgate Software. Privacy Policy