E
E
ebanytiu_lis2017-09-24 22:42:30
SQL Server
ebanytiu_lis, 2017-09-24 22:42:30

Triggers on MSSQL?

Okay... Let's say we have a query UPDATE users SET n = n + 123123 WHERE id = 581;
How can I get 123123 and 581 from this request and use it in a trigger?
Can you give an example, sir?
Or just explain me this code line by line please, I think there is a solution to my question here:

USE SampleDb;

/* Таблица AuditBudget используется в качестве 
журнала логов действий в таблице Project */
GO
CREATE TABLE AuditBudget (
    ProjectNumber CHAR(4) NULL,
    UserName CHAR(16) NULL,
    Date DATETIME NULL,
    BudgetOld FLOAT NULL,
    BudgetNew FLOAT NULL
);

GO
CREATE TRIGGER trigger_ModifyBudget
    ON Project AFTER UPDATE
    AS IF UPDATE(budget)
BEGIN
    DECLARE @budgetOld FLOAT
    DECLARE @budgetNew FLOAT
    DECLARE @projectNumber CHAR(4)

    SELECT @budgetOld = (SELECT Budget FROM deleted)
    SELECT @budgetNew = (SELECT Budget FROM inserted)
    SELECT @projectNumber = (SELECT Number FROM deleted)

    INSERT INTO AuditBudget VALUES
        (@projectNumber, USER_NAME(), GETDATE(), @budgetOld, @budgetNew)
END

Answer the question

In order to leave comments, you need to log in

2 answer(s)
D
d-stream, 2017-09-24
@ebanytiu_lis

inserted - a pseudo-table of inserted values ​​instead of deleted ones deleted,
that is, in deleted there are values ​​that were, and in inserted - new ones
in the case when you didn’t shoot yourself in the foot and the primary key is present - you can really identify what exactly changed, from what and to what .

E
Edward, 2017-09-25
@edb

and note that the above code will only log one record, even if many records are affected by the DML query.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question