Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trigger


Trigger

Author
Message
chandrasekaran.ganapathy
chandrasekaran.ganapathy
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 92
Hi, I have created the Trigger for insertion, Deletion, and Updation.

But the thing is, Suppose If I have multiple insertion, trigger fires that much times.

Do there any to restrict this, we can't do any thing.

Ex:

Insert into table values(' ')
Insert into table values(' ')
Insert into table values(' ')

If I insert three values, trigger fires three time. I agree that this is the nature. But any way to control, if i give multiple insert, it should do at the end.... Probably this might be a stupid question. But please answer this?
mister.magoo
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2270 Visits: 7827
It is not clear what you want to do if there are multiple rows being updated.

If you want the trigger not to fire for multiple row updates then you can do this

create trigger.....
AS
IF (SELECT COUNT(*) FROM inserted)>1 RETURN
IF (SELECT COUNT(*) FROM deleted)>1 RETURN



If you want to handle multiple updates/inserts then you need to make sure everything in the trigger is set based.

e.g.
create trigger....
as
insert mytable(<column-list>Wink select <column-list> from inserted where <selections>



MM


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • chandrasekaran.ganapathy
    chandrasekaran.ganapathy
    Valued Member
    Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

    Group: General Forum Members
    Points: 72 Visits: 92
    Now I'll tell you clearly...

    I am having employee table.

    Suppose If today 10 new joinees Joined. So I'll add through Insert statement.

    SO I will give 10 insert statement. right?

    But here I have a trigger for Insert, Update,Delete.

    My requirement is at the insertion end, trigger should work.

    But here 10 times trigger is triggered. Since i am using 10 insert.

    So Is there anything to control, if i use multiple insert trigger should work at the end of the last insert.
    chandrasekaran.ganapathy
    chandrasekaran.ganapathy
    Valued Member
    Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

    Group: General Forum Members
    Points: 72 Visits: 92
    Now I'll tell you clearly...

    I am having employee table.

    Suppose If today 10 new joinees Joined. So I'll add through Insert statement.

    SO I will give 10 insert statement. right?

    But here I have a trigger for Insert, Update,Delete.

    My requirement is at the insertion end, trigger should work.

    But here 10 times trigger is triggered. Since i am using 10 insert.

    So Is there anything to control, if i use multiple insert trigger should work at the end of the last insert.

    Please let me know for further info.
    vidya_pande
    vidya_pande
    SSC Eights!
    SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)

    Group: General Forum Members
    Points: 891 Visits: 242
    in SQL Server its statement level trigger. For each statement trigger is fired. There is not any solution for the scenario you are thinking of



    chandrasekaran.ganapathy
    chandrasekaran.ganapathy
    Valued Member
    Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

    Group: General Forum Members
    Points: 72 Visits: 92
    Do I need to change the trigger function also, I can. This is my req.

    Please help on this....
    Kingston Dhasian
    Kingston Dhasian
    Hall of Fame
    Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

    Group: General Forum Members
    Points: 3003 Visits: 5007
    Why such a strange requirement? What is the problem if the trigger gets called 10 times? But still if you want the trigger to be called only once try converting your 10 statements into one.

    One way to do this would be to insert all the records in a temporary table without the trigger and then insert in the permanent table with the trigger at one go with an INSERT...SELECT method.

    But i wonder what benefit you will derive out of this?Unsure


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/
    vidya_pande
    vidya_pande
    SSC Eights!
    SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)SSC Eights! (891 reputation)

    Group: General Forum Members
    Points: 891 Visits: 242
    I wanted to say that you can not have trigger called only once for many insert statements.

    I think u could have done this for the below statement-

    insert into Table1 (col1, col2) Select col1,col2 from Table2

    Here trigger is fired only once.



    chandrasekaran.ganapathy
    chandrasekaran.ganapathy
    Valued Member
    Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

    Group: General Forum Members
    Points: 72 Visits: 92
    First of all, Thanks to kingston....

    Really rocked. This is really help ful.
    chandrasekaran.ganapathy
    chandrasekaran.ganapathy
    Valued Member
    Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

    Group: General Forum Members
    Points: 72 Visits: 92
    Thanks vidya..I'll use kingston's suggestion.. That is the best one.... Thanks a lot
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search