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
So, is there a way to preserve current_user
, without giving the dbuser group role direct access to the relations in schema private?
You may be able to use a rule, rather than an INSTEAD OF
trigger, to provide write access through the view. Views always act with the security rights of the view creator rather than the querying user, but I don't think current_user
changes.
If your application connects directly as the user, you can check session_user
instead of current_user
. This also works if you connect with a generic user then SET SESSION AUTHORIZATION
. It won't work if you connect as a generic user then SET ROLE
to the desired user, though.
There is no way to obtain the immediately prior user from within a SECURITY DEFINER
function. You can only get the current_user
and session_user
. A way to get the last_user
or a stack of user identities would be nice, but is not currently supported.
Best Answer
You can disable the trigger in the database function, but any actions on the table with the trigger that occur outside of that transaction (functions create implicit transactions) will be blocked until the function completes.
Simple test case: