How about changing the trigger to a BEFORE UPDATE? That way you can check the values before any action has taken place.
EDIT
OK so I think I understand what you are trying to do. How about this:
ALTER TRIGGER [dbo].[killertrigg]
ON [dbo].[tbl_pvporderview]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
Merge killer as k
Using (select serial, [kill] from inserted) as I (serial, [kill])
On (k.serial = I.serial)
When matched then
Update set [kill] = I.[kill]
When not matched then
Insert (serial, [kill]) values (I.serial, I.[kill]);
END
First, you don't need the IF EXISTS
part in your trigger code. You have the exact same code in the second part so if there are 0 rows produced, you'll be inserting 0 rows anyway:
CREATE TRIGGER [dbo].[trFooInsert] on [dbo].[Foo]
AFTER INSERT
AS
BEGIN
INSERT INTO Foo(ID, Etc, Flag)
SELECT a.ID, a.Etc, 1 FROM Inserted a
LEFT JOIN Foo b ON b.Flag = 1 AND a.ID = b.ID
WHERE b.Flag IS NULL AND a.Flag = 0
END ; -- trigger
which can also be written as:
CREATE TRIGGER [dbo].[trFooInsert] on [dbo].[Foo]
AFTER INSERT
AS
BEGIN
INSERT INTO Foo(ID, Etc, Flag)
SELECT a.ID, a.Etc, 1
FROM Inserted a
WHERE a.Flag = 0
AND NOT EXISTS
( SELECT *
FROM Foo b
WHERE b.Flag = 1 AND a.ID = b.ID
)
END ; -- trigger
So, you want any insert on the table with Flag=0
to also trigger another insert, with same data but Flag=1
(unless there is already a row in Foo
with that ID
and Flag=1
.
Second, and what the actual problem is about, Inserted
is a special table with all the (just) "inserted" rows, not the full table you inserted into. It obviously has less (than or equal) rows as Foo
after the insert. So a join to it will not give same results as a join to Foo
.
The trigger that produces the multiple unwanted rows had Inserted b
instead of Foo b
so it does this:
For any insert on the table with Flag=0
it also triggered another insert, with same data but Flag=1
(unless this same insert had such a row inserted).
You can read more about the special Inserted
and Deleted
tables at the MSDN:
How to use the inserted and deleted Tables
Best Answer
I'm going to assume you have some sort of identity or unique key column on your
SI
table. (This will be a little more difficult if you haven't.)I'm also going to assume your
autoInsert
column is a BIT data type. This means the only values we can possibly have are 1, 0 or NULL. And you want the value updated to 0.And finally, I'm going to assume you only care about updates to the
autoInsert
column.If that's all true, try this:
If, however, your
autoInsert
column is an INT (or similar) data type, you can add the where clause to only cover values of 1 or NULL: