are after triggers really after?

The other day when I was preparing for Microsoft exam 70-229 and I was reading up on triggers and I noticed that the documentation states that the default behavoir of triggers is to fire after the update\insert\delete transaction on the table that the trigger is created on. However, I have noticed that this is not totally accurate. If you setup a trace in profiler with the with all of the xxxstarted and xxxcompleted events for the T-SQL and stored procedure event classes you will notice that the originating transaction starts, then the trigger starts, then the trigger completes and then the originating transaction completes.

So after thinking about this for a while, I have to come to the conclusion that sql server must consider the trigger as part of the orignating transaction, and must therefore complete the trigger before the originating transaction commits. With this in mind, should'nt the default behavoir of triggers be considered "during" and not "after".I believe they fire after the update\insert\delete, but before the commit. They certainly occur during the transaction, because an error in a poorly written trigger can roll back the whole thing.|||I believe they fire after the update\insert\delete, but before the commit. They certainly occur during the transaction, because an error in a poorly written trigger can roll back the whole thing.

I think both statements are accurate. What differentiates an 'after' trigger from a before (or INSTEAD OF) trigger is that it (the INSTEAD OF trigger) executes before any transaction is opened on the underlying table/view. Perhaps a blinding statement of the obvious, but as often the case in semantics, the BEFORE trigger needed an 'opposite' and that opposite became 'after'.

Regards,

hmscott

APP_NAME() empty when deleting

I was trying to set up auditing triggers on a table and was using APP_NAME()
to get the name of the application that performed the action. APP_NAME()
always returns "SQL Server Management Studio - Query" when I'm testing in
that environment and executing INSERTs or UPDATEs. But for some unknown
reason, it returns blank when executing DELETE.
I haven't explicitly set the application name but I was expecting it to
still show "SQL Server Management Studio" here, especially since DELETEs are
as worthy of auditing as anything else. Is this by design? Any idea why?
Jon
>I was trying to set up auditing triggers on a table and was using
>APP_NAME() to get the name of the application that performed the action.
>APP_NAME() always returns "SQL Server Management Studio - Query" when I'm
>testing in that environment and executing INSERTs or UPDATEs. But for some
>unknown reason, it returns blank when executing DELETE.
I can't seem to repro using the script below. What SQL Server version and
service pack are you using?
CREATE TABLE dbo.MyTable
(
MyColumn int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY
)
GO
CREATE TABLE dbo.MyTableAudit
(
AuditID int IDENTITY NOT NULL
CONSTRAINT PK_MyTableAudit PRIMARY KEY,
MyColumn int NOT NULL,
TableAction varchar(20) NOT NULL,
ActionDateTime datetime NOT NULL,
Application nvarchar(128) NULL,
UserName nvarchar(128) NULL,
)
GO
CREATE TRIGGER TR_MyTable_Insert
ON dbo.MyTable FOR INSERT
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Insert', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM inserted
GO
CREATE TRIGGER TR_MyTable_Update
ON dbo.MyTable FOR UPDATE
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Update (before)', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM deleted
UNION ALL
SELECT MyColumn, 'Update (after)', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM inserted
GO
CREATE TRIGGER TR_MyTable_Delete
ON dbo.MyTable FOR DELETE
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Delete', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM deleted
GO
INSERT INTO dbo.MyTable VALUES(1)
GO
UPDATE dbo.MyTable
SET MyColumn = 2
GO
DELETE FROM dbo.MyTable
GO
SELECT * FROM dbo.MyTableAudit
GO
DROP TABLE dbo.MyTable, dbo.MyTableAudit
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:OhB8h2cjHHA.3120@.TK2MSFTNGP05.phx.gbl...
>I was trying to set up auditing triggers on a table and was using
>APP_NAME() to get the name of the application that performed the action.
>APP_NAME() always returns "SQL Server Management Studio - Query" when I'm
>testing in that environment and executing INSERTs or UPDATEs. But for some
>unknown reason, it returns blank when executing DELETE.
> I haven't explicitly set the application name but I was expecting it to
> still show "SQL Server Management Studio" here, especially since DELETEs
> are as worthy of auditing as anything else. Is this by design? Any idea
> why?
> Jon
>

APP_NAME() empty when deleting

I was trying to set up auditing triggers on a table and was using APP_NAME()
to get the name of the application that performed the action. APP_NAME()
always returns "SQL Server Management Studio - Query" when I'm testing in
that environment and executing INSERTs or UPDATEs. But for some unknown
reason, it returns blank when executing DELETE.
I haven't explicitly set the application name but I was expecting it to
still show "SQL Server Management Studio" here, especially since DELETEs are
as worthy of auditing as anything else. Is this by design? Any idea why?
Jon>I was trying to set up auditing triggers on a table and was using
>APP_NAME() to get the name of the application that performed the action.
>APP_NAME() always returns "SQL Server Management Studio - Query" when I'm
>testing in that environment and executing INSERTs or UPDATEs. But for some
>unknown reason, it returns blank when executing DELETE.
I can't seem to repro using the script below. What SQL Server version and
service pack are you using?
CREATE TABLE dbo.MyTable
(
MyColumn int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY
)
GO
CREATE TABLE dbo.MyTableAudit
(
AuditID int IDENTITY NOT NULL
CONSTRAINT PK_MyTableAudit PRIMARY KEY,
MyColumn int NOT NULL,
TableAction varchar(20) NOT NULL,
ActionDateTime datetime NOT NULL,
Application nvarchar(128) NULL,
UserName nvarchar(128) NULL,
)
GO
CREATE TRIGGER TR_MyTable_Insert
ON dbo.MyTable FOR INSERT
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Insert', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM inserted
GO
CREATE TRIGGER TR_MyTable_Update
ON dbo.MyTable FOR UPDATE
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Update (before)', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM deleted
UNION ALL
SELECT MyColumn, 'Update (after)', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM inserted
GO
CREATE TRIGGER TR_MyTable_Delete
ON dbo.MyTable FOR DELETE
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Delete', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM deleted
GO
INSERT INTO dbo.MyTable VALUES(1)
GO
UPDATE dbo.MyTable
SET MyColumn = 2
GO
DELETE FROM dbo.MyTable
GO
SELECT * FROM dbo.MyTableAudit
GO
DROP TABLE dbo.MyTable, dbo.MyTableAudit
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:OhB8h2cjHHA.3120@.TK2MSFTNGP05.phx.gbl...
>I was trying to set up auditing triggers on a table and was using
>APP_NAME() to get the name of the application that performed the action.
>APP_NAME() always returns "SQL Server Management Studio - Query" when I'm
>testing in that environment and executing INSERTs or UPDATEs. But for some
>unknown reason, it returns blank when executing DELETE.
> I haven't explicitly set the application name but I was expecting it to
> still show "SQL Server Management Studio" here, especially since DELETEs
> are as worthy of auditing as anything else. Is this by design? Any idea
> why?
> Jon
>

APP_NAME() empty when deleting

I was trying to set up auditing triggers on a table and was using APP_NAME()
to get the name of the application that performed the action. APP_NAME()
always returns "SQL Server Management Studio - Query" when I'm testing in
that environment and executing INSERTs or UPDATEs. But for some unknown
reason, it returns blank when executing DELETE.
I haven't explicitly set the application name but I was expecting it to
still show "SQL Server Management Studio" here, especially since DELETEs are
as worthy of auditing as anything else. Is this by design? Any idea why?
Jon>I was trying to set up auditing triggers on a table and was using
>APP_NAME() to get the name of the application that performed the action.
>APP_NAME() always returns "SQL Server Management Studio - Query" when I'm
>testing in that environment and executing INSERTs or UPDATEs. But for some
>unknown reason, it returns blank when executing DELETE.
I can't seem to repro using the script below. What SQL Server version and
service pack are you using?
CREATE TABLE dbo.MyTable
(
MyColumn int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY
)
GO
CREATE TABLE dbo.MyTableAudit
(
AuditID int IDENTITY NOT NULL
CONSTRAINT PK_MyTableAudit PRIMARY KEY,
MyColumn int NOT NULL,
TableAction varchar(20) NOT NULL,
ActionDateTime datetime NOT NULL,
Application nvarchar(128) NULL,
UserName nvarchar(128) NULL,
)
GO
CREATE TRIGGER TR_MyTable_Insert
ON dbo.MyTable FOR INSERT
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Insert', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM inserted
GO
CREATE TRIGGER TR_MyTable_Update
ON dbo.MyTable FOR UPDATE
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Update (before)', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM deleted
UNION ALL
SELECT MyColumn, 'Update (after)', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM inserted
GO
CREATE TRIGGER TR_MyTable_Delete
ON dbo.MyTable FOR DELETE
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Delete', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM deleted
GO
INSERT INTO dbo.MyTable VALUES(1)
GO
UPDATE dbo.MyTable
SET MyColumn = 2
GO
DELETE FROM dbo.MyTable
GO
SELECT * FROM dbo.MyTableAudit
GO
DROP TABLE dbo.MyTable, dbo.MyTableAudit
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:OhB8h2cjHHA.3120@.TK2MSFTNGP05.phx.gbl...
>I was trying to set up auditing triggers on a table and was using
>APP_NAME() to get the name of the application that performed the action.
>APP_NAME() always returns "SQL Server Management Studio - Query" when I'm
>testing in that environment and executing INSERTs or UPDATEs. But for some
>unknown reason, it returns blank when executing DELETE.
> I haven't explicitly set the application name but I was expecting it to
> still show "SQL Server Management Studio" here, especially since DELETEs
> are as worthy of auditing as anything else. Is this by design? Any idea
> why?
> Jon
>