What is the correct syntax for creating a database trigger for inserting, modifying, and deleting

Question

I have what looks like a basic script to run db on a SQL server, and I ran into a problem.

I have a Users table (id, name, phone, etc.) and I have a UsersHistory table (id, user_id action, fields, timestamp)

I want a database trigger where users are inserted, updated or deleted at any time, I want to create a new record created in UsersHistory with the user ID and the action to be performed (insert new, updated fields, remote identifier. Table.

this is how far i got, but i cant figure out how:

• Get an identifier to modify and delete, as well as
• How to get a list of fields that have been changed, and the action that was performed (insert, delete, update).

---

CREATE TRIGGER Update_Users_History 
   ON  Users
   AFTER INSERT,DELETE,UPDATE
 AS 
 BEGIN
-- Insert statements for trigger here

insert into UsersHistory (user_id, [action], [fields], timestamp)
select  max(id) as user_id, {action ??},{fields??}  getdate() from Users)

END
GO

any suggestions?

Answer 1

The easiest way is to simply create three triggers - one for each operation:

CREATE TRIGGER trgUserInsert
ON dbo.User AFTER INSERT
AS BEGIN
   INSERT INTO dbo.UserHistory............
END     

CREATE TRIGGER trgUserDelete
ON dbo.User AFTER DELETE
AS BEGIN
   INSERT INTO dbo.UserHistory............
END 

CREATE TRIGGER trgUserUpdate
ON dbo.User AFTER UPDATE
AS BEGIN
   INSERT INTO dbo.UserHistory............
END 

Thus, everything is simple, and you easily understand what you are doing, plus it gives you the ability to disable the trigger for one operation if, for example, you need to insert or delete a huge list of elements.

"-" - Inserted ( INSERT UPDATE) Deleted ( UPDATE DELETE). ( UPDATE) , ( DELETE) ( UPDATE).

, , , . Inserted Deleted .

" ", ( , ...):

CREATE TRIGGER trgUserInsert
ON dbo.User AFTER INSERT
AS BEGIN
   INSERT INTO 
      dbo.UserHistory(UserID, Action, DateTimeStamp, AuditMessage)
      SELECT 
         i.UserID, 'INSERT', getdate(), 'User inserted into table'
      FROM
         Inserted i
END     

, "" ? - , . - Inserted Updated:

• , UPDATE
• Inserted , Deleted , INSERT
• Inserted , Deleted , DELETE

" , " - , . "", ,

IF UPDATE(fieldname) ......

.

COLUMNS_UPDATED() - , , , VARBINARY , , . .....

---

, - , , User_History:

CREATE TRIGGER trgUser_Universal
ON dbo.Users
AFTER INSERT, UPDATE, DELETE
AS BEGIN
  DECLARE @InsHasRows BIT = 0
  DECLARE @DelHasRows BIT = 0

  IF EXISTS(SELECT TOP 1 * FROM INSERTED)
    SET @InsHasRows = 1

  IF EXISTS(SELECT TOP 1 * FROM DELETED)
    SET @DelHasRows = 1

  DECLARE @TriggerAction VARCHAR(20)

  IF @InsHasRows = 1 AND @DelHasRows = 1 
     SET @TriggerAction = 'UPDATE'
  ELSE
     IF @InsHasRows = 1
        SET @TriggerAction = 'INSERT'
     ELSE  
        SET @TriggerAction = 'DELETE'

  IF @InsHasRows = 1
    INSERT INTO dbo.UsersHistory(user_id, [action], [fields], timestamp)
      SELECT i.UserId, @TriggerAction, null, getdate()
      FROM INSERTED i
  ELSE  
    INSERT INTO dbo.UsersHistory(user_id, [action], [fields], timestamp)
      SELECT d.UserId, @TriggerAction, null, getdate()
      FROM DELETED d
END

, , - : -)

---

?

Answer 2

"". - DELETED, - INSERTED. , DELETED. , INSERTED. , DELETED, INSERTED. DELETED INSERTED , , .

, , , , , . UPDATE, INSERT DELETE.