After insert trigger fires even though no insert actually occured

3 Nov 2005 4:40 PM

Steve'o

SQL Server 2000 SP3a

Have I totally missunderstood and AFTER INSERT, UPDATE trigger as mine fires
on an insert being run but with no actual inserts occuring.

ie I have a sproc which inserts into a table, I've checked the select
statement of the insert and it returns no rows, so I presumed this meant that
the trigger would not fire as no rows are being inserted, but it does.

======================
-- The basic spoc is this, the select returns no rows, so no inserts should
occur??:
insert into table_a (
a_col_1,
a_col_2)
select
b_col_1,
b_col_2
from some_table b
left outer join table_a on
b.pkey = a.pkey
where
a.pkey is null
=======================

======================
-- The basic trigger is
Create trigger tg_Trigger_Name
on A_Table
after insert, update

if update (col_a)
begin
update some other table set some stuff
end
======================

I've looked through BOL, but it simply sais after insert/update trigger
fires after completion.

Thanks for any pointers.

3 Nov 2005 4:48 PM

Aaron Bertrand [SQL Server MVP]

> if update (col_a)
> begin
> update some other table set some stuff
> end

The trigger will still fire, even if no rows are affected. So, you should
base any activities in the trigger on whether or not anything actually
happened. I typically wrap my DML trigger activities inside of the
following:

IF @@ROWCOUNT > 0
BEGIN
... stuff here
END

For an insert trigger, you could equally say:

IF EXISTS (SELECT 1 FROM inserted)
BEGIN
... stuff here
END

A

3 Nov 2005 4:48 PM

Leonid Bogdanov

This is the way it works. This is not bad, if your actions inside the
trigger are based on contents of the inserted table, because this table is
empty. However, you can use the following code to skip the logic inside the
trigger (the same is true for updates):

if not exists(select * from inserted)
return

Leonid.

Show quote

"Steve'o" <Ste***@discussions.microsoft.com> wrote in message
news:55564004-68B8-4879-9F24-7BE5CF2E8F43@microsoft.com...
> SQL Server 2000 SP3a
>
> Have I totally missunderstood and AFTER INSERT, UPDATE trigger as mine
> fires
> on an insert being run but with no actual inserts occuring.
>
> ie I have a sproc which inserts into a table, I've checked the select
> statement of the insert and it returns no rows, so I presumed this meant
> that
> the trigger would not fire as no rows are being inserted, but it does.
>
> ======================
> -- The basic spoc is this, the select returns no rows, so no inserts
> should
> occur??:
> insert into table_a (
> a_col_1,
> a_col_2)
> select
> b_col_1,
> b_col_2
> from some_table b
> left outer join table_a on
> b.pkey = a.pkey
> where
> a.pkey is null
> =======================
>
> ======================
> -- The basic trigger is
> Create trigger tg_Trigger_Name
> on A_Table
> after insert, update
>
> if update (col_a)
> begin
> update some other table set some stuff
> end
> ======================
>
> I've looked through BOL, but it simply sais after insert/update trigger
> fires after completion.
>
> Thanks for any pointers.

3 Nov 2005 5:29 PM

Steve'o

Aaron, Leonid, BIG thanks for the help! The problem I have is that the
triggers after fire so many other triggers that it takes a long time, so
avoiding it would be great, thanks for the examples!

I've started to change update statement's in the other triggers to include a
where old_value <> inserted_new_value so to avoid updates where its pointless.

Thanks again!

Show quote

"Leonid Bogdanov" wrote:

> This is the way it works. This is not bad, if your actions inside the
> trigger are based on contents of the inserted table, because this table is
> empty. However, you can use the following code to skip the logic inside the
> trigger (the same is true for updates):
>
> if not exists(select * from inserted)
> return
>
> Leonid.
>
>
> "Steve'o" <Ste***@discussions.microsoft.com> wrote in message
> news:55564004-68B8-4879-9F24-7BE5CF2E8F43@microsoft.com...
> > SQL Server 2000 SP3a
> >
> > Have I totally missunderstood and AFTER INSERT, UPDATE trigger as mine
> > fires
> > on an insert being run but with no actual inserts occuring.
> >
> > ie I have a sproc which inserts into a table, I've checked the select
> > statement of the insert and it returns no rows, so I presumed this meant
> > that
> > the trigger would not fire as no rows are being inserted, but it does.
> >
> > ======================
> > -- The basic spoc is this, the select returns no rows, so no inserts
> > should
> > occur??:
> > insert into table_a (
> > a_col_1,
> > a_col_2)
> > select
> > b_col_1,
> > b_col_2
> > from some_table b
> > left outer join table_a on
> > b.pkey = a.pkey
> > where
> > a.pkey is null
> > =======================
> >
> > ======================
> > -- The basic trigger is
> > Create trigger tg_Trigger_Name
> > on A_Table
> > after insert, update
> >
> > if update (col_a)
> > begin
> > update some other table set some stuff
> > end
> > ======================
> >
> > I've looked through BOL, but it simply sais after insert/update trigger
> > fires after completion.
> >
> > Thanks for any pointers.
>
>
>

3 Nov 2005 11:18 PM

Hugo Kornelis

On Thu, 3 Nov 2005 09:29:11 -0800, Steve'o wrote:

>Aaron, Leonid, BIG thanks for the help! The problem I have is that the
>triggers after fire so many other triggers that it takes a long time, so
>avoiding it would be great, thanks for the examples!
>
>I've started to change update statement's in the other triggers to include a
>where old_value <> inserted_new_value so to avoid updates where its pointless.

Hi Steve'o,

That's good. Don't forget that NULL = NULL is never true, but NULL <>
NULL is never true either. If your columns can be null, you'll need a
more complicated test.

Either
WHERE old_value <> new_value
OR ( old_value IS NULL AND new_value IS NOT NULL )
OR ( old_value IS NOT NULL AND new_value IS NULL )

or
WHERE NULLIF(old_value, new_value) IS NOT NULL
OR NULLIF(new_value, old_value) IS NOT NULL

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)