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