CODEWITHSUNDEEP
sql-serverlogging
RikaJCostaL
RikaJCostaL

Reputation: 1

SQL After delete, insert or update trigger

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

Upvotes: 0

Views: 84

Answers (1)

Pratik Bhavsar
Pratik Bhavsar

Reputation: 848

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. :)

Upvotes: 1

Related Questions