sql server - update - Insertar/eliminar/actualizar desencadenante en el servidor SQL

update select sql server 2014

Question

Estoy intentando producir un desencadenador de borrado / inserción / actualización todo en uno. Obtengo dos "sintaxis incorrecta cerca de DESPUÉS en el segundo y tercer AFTERS y un error de sintaxis cerca del último FINAL.

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

Con suerte, esta es suficiente información. Creo que el problema es mi sintaxis pero no puedo encontrar la sintaxis correcta en línea.

Answer 1

Estoy de acuerdo con la respuesta de @Vishnu. Me gustaría agregar que si desea usar el usuario de la aplicación en su activador, puede usar "context_info" para pasar la información al activador.

Me pareció muy útil hacer esto a continuación: http://jasondentler.com/blog/2010/01/exploiting-context_info-for-fun-and-audit

Answer 2

Lo que le estoy dando es el código de activación para INSERT, UPDATE y DELETE. Esto funciona bien en Microsoft SQL SERVER 2008 y en adelante la base de datos que estoy usando es Northwind

/* comment section first create a table to keep track of Insert, Delete, Update
create table Emp_Audit(
EmpID int,
Activity varchar(20),
DoneBy varchar(50),
Date_Time datetime NOT NULL DEFAULT GETDATE()
);
select * from Emp_Audit*/
create trigger Employee_trigger
on Employees
after UPDATE, INSERT, DELETE
as
declare @EmpID int,@user varchar(20), @activity varchar(20);
if exists(SELECT * from inserted) and exists (SELECT * from deleted)
begin
SET @activity = ''UPDATE'';
SET @user = SYSTEM_USER;
SELECT @EmpID = EmployeeID from inserted i;
INSERT into Emp_Audit(EmpID,Activity, DoneBy) values (@EmpID,@activity,@user);
end
If exists (Select * from inserted) and not exists(Select * from deleted)
begin
SET @activity = ''INSERT'';
SET @user = SYSTEM_USER;
SELECT @EmpID = EmployeeID from inserted i;
INSERT into Emp_Audit(EmpID,Activity, DoneBy) values(@EmpID,@activity,@user);
end
If exists(select * from deleted) and not exists(Select * from inserted)
begin
SET @activity = ''DELETE'';
SET @user = SYSTEM_USER;
SELECT @EmpID = EmployeeID from deleted i;
INSERT into Emp_Audit(EmpID,Activity, DoneBy) values(@EmpID,@activity,@user);
end

Answer 3

Lo uso para todos los estados (actualizar, insertar y eliminar)

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

Answer 4

No es posible, por MSDN :

Puede ejecutar el mismo código para varios tipos de activadores, pero la sintaxis no permite múltiples bloques de códigos en un activador:

Activación en una instrucción INSERT, UPDATE o DELETE a una tabla o vista (Activación de DML)

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 [ ; ] > }