SQL TRIGGER detect UPDATED or DELETED-CodePudding

Here my code to create trigger

CREATE TRIGGER TRIGGERNAME ON [TABLENAME]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
     --[SQL STATEMENTS]
END

In the SQL statement, how can I check things like:

if (a row is updated) {
    -- do something
} 
else if (a row is deleted) {
    -- do something else
}

CodePudding user response：

CREATE TRIGGER [TRIGGERNAME] ON [TABLENAME]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @DELETED INT, @UPDATED INT
    SELECT @DELETED = COUNT(*) FROM [deleted]
    SELECT @UPDATED = COUNT(*) FROM [inserted]
    IF @UPDATED > 0
            PRINT ' inserted  '
        END
    ELSE IF @DELETED > 0
        BEGIN
            PRINT ' deleted '
        END
END

CodePudding user response：

In oracle you can write like

CREATE TRIGGER [TRIGGERNAME] ON [TABLENAME] AFTER INSERT, UPDATE, DELETE AS BEGIN

IF INSERTING    THEN
-- Some business logic

END IF;

IF UPDATING    THEN
-- Some business logic
END IF;

IF DELETING    THEN
-- Some business logic
END IF;

END

CodePudding user response：

Actually, the trigger is only for insert/delete:

IF EXISTS (SELECT 1 FROM inserted)
   -- I am an insert
ELSE
   -- I am a delete

But if you want to create the trigger for update and delete action, you can try this:

CREATE TRIGGER [TRIGGERNAME] ON [TABLENAME]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    IF EXISTS (SELECT 1 FROM inserted)
    BEGIN
      IF EXISTS (SELECT 1 FROM deleted)
      BEGIN
        PRINT 'I am an update'
      END
    END
    ELSE
    BEGIN
      PRINT 'I am a delete'
    END
END

You can see this reference to help you more.