After insert trigger fires even though no insert actually occured

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 tha
t
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.> 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|||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" <Steveo@.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.