Trigger

Question

Post reply

Trigger

chandrasekaran.ganapathy

SSCommitted

Points: 1738
More actions
March 23, 2010 at 4:05 am

#218439

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?
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

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>) select <column-list> from inserted where <selections>

MM

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

chandrasekaran.ganapathy SSCommitted Points: 1738 More actions · Answer 2

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 SSCommitted Points: 1738 More actions · Answer 3

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 SSCertifiable Points: 5275 More actions · Answer 4

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 SSCommitted Points: 1738 More actions · Answer 5

Do I need to change the trigger function also, I can. This is my req.

Please help on this....

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 6

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 SSCertifiable Points: 5275 More actions · Answer 7

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 SSCommitted Points: 1738 More actions · Answer 8

First of all, Thanks to kingston....

Really rocked. This is really help ful.

chandrasekaran.ganapathy SSCommitted Points: 1738 More actions · Answer 9

Thanks vidya..I'll use kingston's suggestion.. That is the best one.... Thanks a lot

Mithra SSChasing Mays Points: 639 More actions · Answer 10

Guys,

Can someone give me a script that fires a trigger whenever sysadmin access is granted to a builin\adminstrator login.

Many thanks,

Boj

Mithra SSChasing Mays Points: 639 More actions · Answer 11

Mithra

SSChasing Mays

Points: 639

August 14, 2010 at 6:03 am

#1207029

..

movnabbs Grasshopper Points: 14 More actions · Answer 12

good answer from kingston ,

here is simple and easy implementation for stopping trigger on insert level

keep on giving answers