I am trying to produce an all-in-one delete/insert/update trigger. I get two "incorrect syntax near AFTER at the second and third AFTERS and a syntax error near the last END.
CREATE TRIGGER trig_all_dml
ON [dbo.file]
AFTER UPDATE
AS BEGIN
UPDATE
(excess code)
END
AFTER INSERT
AS BEGIN
UPDATE
(excess code)
END
AFTER DELETE
AS BEGIN
UPDATE (excess code)
END
GO
Hopefully, this is enough information. I think the problem is my syntax but I can't find the correct syntax online.
This question is related to
sql-server
triggers
I agree with @Vishnu's answer. I would like to add that if you want to use the application user in your trigger you can use "context_info" to pass the info to the trigger.
I found following very helpful in doing that: http://jasondentler.com/blog/2010/01/exploiting-context_info-for-fun-and-audit
Not possible, per MSDN:
You can have the same code execute for multiple trigger types, but the syntax does not allow for multiple code blocks in one trigger:
Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
CREATE TRIGGER [ schema_name . ]trigger_name ON { table | view } [ WITH <dml_trigger_option> [ ,...n ] ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [ NOT FOR REPLICATION ] AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }
I use that for all status (update, insert and delete)
CREATE TRIGGER trg_Insert_Test
ON [dbo].[MyTable]
AFTER UPDATE, INSERT, DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Activity NVARCHAR (50)
-- update
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
BEGIN
SET @Activity = 'UPDATE'
END
-- insert
IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted)
BEGIN
SET @Activity = 'INSERT'
END
-- delete
IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS(SELECT * FROM inserted)
BEGIN
SET @Activity = 'DELETE'
END
-- delete temp table
IF OBJECT_ID('tempdb..#tmpTbl') IS NOT NULL DROP TABLE #tmpTbl
-- get last 1 row
SELECT * INTO #tmpTbl FROM (SELECT TOP 1 * FROM (SELECT * FROM inserted
UNION
SELECT * FROM deleted
) AS A ORDER BY A.Date DESC
) AS T
-- try catch
BEGIN TRY
INSERT INTO MyTable (
[Code]
,[Name]
.....
,[Activity])
SELECT [Code]
,[Name]
,@Activity
FROM #tmpTbl
END TRY BEGIN CATCH END CATCH
-- delete temp table
IF OBJECT_ID('tempdb..#tmpTbl') IS NOT NULL DROP TABLE #tmpTbl
SET NOCOUNT OFF;
END
Source: Stackoverflow.com