Triggers and (Insert Into ) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Triggers and (Insert Into )

Author

Topic

leedo
Starting Member

1 Post

Posted - 2010-12-05 : 12:53:34

How to fire insert trigger foreach row when using
insert into or select from.

I have created an instead of trigger that works perfectly if I add values row by row. However, if values are added to it using (insert into) the trigger doesn't fire.

My porpose is to omit a certain row values based on a certain rule. That is: if the row (CalledNumber) begins with 97 e.g 97223 it should reject that value and exit.

I appreciate any help with this.

Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-05 : 13:00:00

The trigger fires per statement - not possible to get it to fire per row (not easily anyway).
You have to deal with multiple row updates in the trigger or restrict updates to single row (and fail in the trigger if that's not so).

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Sachin.Nand

2937 Posts

Posted - 2010-12-05 : 23:41:15

I think it is a mistake to put the logic in a trigger.
A trigger will fire ONLY ONCE for all the records affested by the insert command.So in that sense for multiple record inserts it will fire only for the last record inserted.

PBUH

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-06 : 04:36:37

quote:
Originally posted by Sachin.Nand

I think it is a mistake to put the logic in a trigger.
A trigger will fire ONLY ONCE for all the records affested by the insert command.So in that sense for multiple record inserts it will fire only for the last record inserted.

PBUH

Nope - it will fire for the transaction or statement. The inserted and deleted tables will hold all of the rows affected not just the last one.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Sachin.Nand

2937 Posts

Posted - 2010-12-06 : 06:09:41

Sorry the trigger will fire just for the first record inserted in the batch and wont fire for any other records in the batch.


create  table T(id int)

GO

Create trigger test on T
for Insert,Update as
Declare @val int
select @val=id%2 from inserted
if @val=1
RollBack

GO

insert into T select 1
GO
insert into T select 2
GO
insert into T select 3
GO
insert into T select 4
GO

insert into T
select 5 union all
select 6 union all
select 7 union all
select 8
GO

select * from t

drop table t

PBUH

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-12-06 : 06:20:41

No, it will fire once for the statement, not for first row, last row (or any other row) nor once per batch. SQL triggers are statement-level triggers and fire once for each statement.

Your example is flawed, you're selecting a value from inserted into a variable when inserted will contain 4 values (in your last example). That's why you're seeing first (or last) row, not because that's how the trigger fires, but because your trigger is ignoring all rows but one.

If you want to rollback any statement that inserts an odd value into the table, you need this instead.

Create trigger test on T
for Insert,Update as
IF EXISTS (SELECT 1 FROM inserted WHERE id%2 = 1) -- set based, there can be more than one row in inserted.
RollBack TRANSACTION
GO

Bear in mind that if you say
SELECT @Var = SomeColumn FROM table
and table has more than one row, there's no guarantee whatsoever which row's value will end in in that variable. It depends on the way SQL processes the query

--
Gail Shaw
SQL Server MVP

Sachin.Nand

2937 Posts

Posted - 2010-12-06 : 07:32:25

quote:
Originally posted by GilaMonster

No, it will fire once for the statement, not for first row, last row (or any other row) nor once per batch. SQL triggers are statement-level triggers and fire once for each statement.

Yeah I understand what you are trying to say.

You said

Create trigger test on T
for Insert,Update as
IF EXISTS (SELECT 1 FROM inserted WHERE id%2 = 1) -- set based, there can be more than one row in inserted.
RollBack TRANSACTION
GO

So what will happen is if we are inserting 4 rows at once and at the 3rd row the trigger rollsbacks the whole transaction which is not I would desire.I will just want the 3rd row not be inserted.Thats the reason I said at the first that having this kind of logic in trigger is not good for multiple inserts.

Also I made a mistake by writing this

quote:
Sorry the trigger will fire just for the first record inserted in the batch and wont fire for any other records in the batch.

I apologise for that.

PBUH

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-06 : 07:41:11

An sql statement is atomic. It completes or fails - it can't half complete.
The trigger is part of the transaction created by the statement - if it rolls back then the whole statement will roll back.

You would have to stop the trigger from failing and handle the logic to delete or not insert rows in the trigger so that the statement completes successfully. In that way you can stop the 3rd row from being inserted while the others are.
Another option is to make sure that the client does row by row inserts with each one being a transaction. This might be an issue as if someone decides to put a transaction round the batch then you will fail the whole batch.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-12-06 : 08:11:36

quote:
Originally posted by Sachin.Nand

So what will happen is if we are inserting 4 rows at once and at the 3rd row the trigger rollsbacks the whole transaction.

Correct. That's one of the ACID requirements of relational databases. Atomicity - an operation either completes entirely or fails entirely. It cannot partially succeed.

If you want some rows to be inserted and others not, an AFTER trigger is the wrong place (the inserts have all happened). You need an Instead Of trigger that replaces the insert and lets you do just what you want.

Or, more correctly, you want some logic in the front end or stored proc to prevent the invalid rows from being inserted in the first place.

--
Gail Shaw
SQL Server MVP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-06 : 08:31:26

Oh I wish SQL Server would support autonomous transactions...

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources