SQL After delete, insert or update trigger

Question

Can you please give me a hand in starting this?

I have a table with this format:

CREATE TABLE [dbo].[ACS_ARMAZEM]
(
    [ARM_ID] [INT] IDENTITY(20000,1) NOT NULL,
    [ARM_DES] [NVARCHAR](50) NOT NULL,
    [ARM_5_COD_DIG] [NVARCHAR](5) NOT NULL,
    [ARM_SIT] [INT] NOT NULL,
    [ARM_BIZ_TIP] [INT] NOT NULL,
    [ARM_STK_CTRL] [INT] NOT NULL,
    [ARM_DAT_CRI] [DATE] NOT NULL,
)

I need to log all changes (insert, delete or update) to a log table I have.

But I want to log the old and new values (even if a new row is inserted I want to log it as null in the old columns).

Best regards,

RL

Well, please show us what YOU have tried so far. We'll be glad to lend a helping hand - but SO isn't a free code-writing service where you just dump your requirements and someone writes the whole code for you .... start by reading the official Microsoft docs on how to create a trigger — marc_s
– marc_s, Commented Mar 16, 2019 at 12:18

Pratik Bhavsar · Accepted Answer · 2019-03-16 13:08:29Z

1

Keeping it short, you need to use inserted and deleted tables. ie.

For insert/update:

UPDATE [YourTable]
   SET [YourColumn] = i + 1
OUTPUT deleted.[YourColumn], inserted.[YourColumn] INTO [YourLogTable]
WHERE i = 1;

(Here, deleted will give old values where inserted gives new)

For delete:

DELETE from [YourTable]
OUTPUT deleted.[YourColumn], NULL INTO [YourLogTable]
WHERE i = 2;

Of course, you will need to modify the statement to match with your log table structure. Please let me know if it helps. :)

answered Mar 16, 2019 at 13:08

Pratik Bhavsar

8481 gold badge10 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL After delete, insert or update trigger

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related