Table with last UpdatedBy Column and LastUpdatedOn Column.
Want to create Insert and Update Triggers that would automatically write
Logged in User Name and System Date Time to each field.
I Think User is SUSER_SNAME function and System date Is GETDATE() but can't
figure out how to write these two to row from within a trigger in table.
Any help greatly appreciated.
BobYou can use the inserted psuedo-table to identify the updated rows. One
method:
CREATE TRIGGER TR__Update_MyTable
ON MyTable FOR UPDATE
AS
UPDATE MyTable
SET UpdatedBy = SUSER_SNAME(),
LastUpdatedOn = GETDATE()
WHERE EXISTS
(
SELECT *
FROM inserted
WHERE inserted.PK = MyTable.PK
)
Hope this helps.
Dan Guzman
SQL Server MVP
"Bob" <bdufour@.sgiims.com> wrote in message
news:uwISI5NzFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Table with last UpdatedBy Column and LastUpdatedOn Column.
> Want to create Insert and Update Triggers that would automatically write
> Logged in User Name and System Date Time to each field.
> I Think User is SUSER_SNAME function and System date Is GETDATE() but
> can't figure out how to write these two to row from within a trigger in
> table.
> Any help greatly appreciated.
> Bob
>|||Use an INSTEAD OF trigger. The deleted pseudotable is a copy of any
existing rows that are about to be updated, the inserted pseudotable
contains the new values. The deleted pseudotable will be empty if the
operation was INSERT. The inserted pseudotable will be empty if the
operation was DELETE. Both pseudotables will have the same number of rows
if the operation was UPDATE. The reason you should use an INSTEAD OF
trigger is that it allows you to change values before it hits the database.
The only problem with them is that the cascade kludge won't coexist, which
in my opinion is a good thing.
In an INSTEAD OF UPDATE trigger, you simply issue an update statement:
UPDATE tableName
SET column1 = inserted.column1,
column2 = inserted.column2,
..,
UpdatedBy = SUSER_SNAME(),
LastUpdatedOn = GETDATE()
FROM inserted
WHERE primaryKeyColumn = inserted.primaryKeyColumn
The only time this is a problem is if you're using natural keys. If that's
the case, then you need to test for multiple rows
put code similar to this at the top of the trigger
--Don't put anything before this!!!
DECLARE @._ROWCOUNT INT SET @._ROWCOUNT = @.@.ROWCOUNT
IF @._ROWCOUNT = 0 RETURN
IF @._ROWCOUNT > 1 AND (UPDATE(primaryKeyColumn1) OR
UPDATE(primaryKeyColumn2))
BEGIN
ROLLBACK TRANSACTION
RAISERROR('ATTEMPT TO UPDATE Primary Key using set-based operation', 16, 0)
RETURN
END
"Bob" <bdufour@.sgiims.com> wrote in message
news:uwISI5NzFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Table with last UpdatedBy Column and LastUpdatedOn Column.
> Want to create Insert and Update Triggers that would automatically write
> Logged in User Name and System Date Time to each field.
> I Think User is SUSER_SNAME function and System date Is GETDATE() but
> can't figure out how to write these two to row from within a trigger in
> table.
> Any help greatly appreciated.
> Bob
>|||Thank you both very much
Bob
"Bob" <bdufour@.sgiims.com> wrote in message
news:uwISI5NzFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Table with last UpdatedBy Column and LastUpdatedOn Column.
> Want to create Insert and Update Triggers that would automatically write
> Logged in User Name and System Date Time to each field.
> I Think User is SUSER_SNAME function and System date Is GETDATE() but
> can't figure out how to write these two to row from within a trigger in
> table.
> Any help greatly appreciated.
> Bob
>
