0

I have a database in which I want to make a centralized trigger in which if any database table is hit with insert,update and delete operation than this trigger should be executed and column values that are inserted,updated or deleted from that operation should be saved in my own table by the help of trigger.

I have seen fn_dblog function in sql server but it does not return column values which are affected. I need to save that column values also which are going to be inserted or updated

  • Sounds like an X-Y problem to me. Are you looking for audit may be? – mustaccio Apr 11 at 16:45
  • 1
    Have you looked into using Temporal Tables? – Jonathan Fite Apr 11 at 16:46
0

I am not sure about the SQL server version that you are using however if you are using SQL Server 2016 and above, you can very much use temporal table for the purpose that you have mentioned.

You may read more about this table from below Microsoft sites:

https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-2017

https://techcommunity.microsoft.com/t5/Premier-Field-Engineering/SQL-2016-Temporal-Tables-What-are-they-and-how-do-you-set-one-up/td-p/371042

I would like to put a bit of insight into this type of table.

You may create an employee table along with temporal table as below:

create table Employee
(
Emp_ID int PRIMARY KEY CLUSTERED,   -- MANDATORY To have Primary Key
Emp_name varchar(15),
Emp_desc varchar(100),
[ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START, --TO AUDIT ROW INSERTION DATE
[ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END,--TO AUDIT ROW UPDATE / DELETE DATE
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)                     
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));-- THIS HISTORY TABLE IS AUTO CREATED

Now, you insert dummy records in this table as below:

insert into Employee(Emp_ID,Emp_name,Emp_desc) values ( 50001,'Steve' ,'Program Manager , 5+ Exp, Moderate Communication Knowledge')
insert into Employee(Emp_ID,Emp_name,Emp_desc) values ( 50002,'Jon' ,'Executive Manager , 10+ Exp, Moderate Domain Knowledge')
insert into Employee(Emp_ID,Emp_name,Emp_desc) values ( 50003,'Little' ,'Executive Manager , 10+ Exp, Moderate Domain Knowledge')
insert into Employee(Emp_ID,Emp_name,Emp_desc) values ( 50004, 'ABC Little' ,'Executive Manager , 10+ Exp, Moderate Domain Knowledge')
insert into Employee(Emp_ID,Emp_name,Emp_desc) values ( 50005,'Michael' ,'Executive Manager , 10+ Exp, Moderate Domain Knowledge')
insert into Employee(Emp_ID,Emp_name,Emp_desc) values ( 50006,'KobL' ,'Executive Manager , 10+ Exp, Moderate Domain Knowledge')
insert into Employee(Emp_ID,Emp_name,Emp_desc) values ( 50007,'George' ,'Executive Manager , 10+ Exp, Moderate Domain Knowledge')
insert into Employee(Emp_ID,Emp_name,Emp_desc) values ( 50008,'James' ,'Executive Manager , 10+ Exp, Moderate Domain Knowledge')

If you select these two tables now:

select * from Employee                          -- 8 ROWS
SELECT * FROM EmployeeHistory -- 0 Rows

Now, you may perform below operation:

UPDATE Employee SET EMP_NAME = 'NAME NEW' WHERE EMP_ID = 50001

select * from Employee                      -- 8 ROWS
SELECT * FROM EmployeeHistory               -- 1 ROWS

DELETE FROM Employee WHERE EMP_ID = 50008     -- 1 ROW IS REMOVED

select * from Employee                      -- 7 ROWS
SELECT * FROM EmployeeHistory               -- 2 ROWS

You can find which operation was performed by comparing Emp_id(Primary key) column in these two table. If primary key is missing from one and available in second, means delete operation was performed. If Id is present however changed then, based on timestamp you can make out at what time which column was changed.

Alternatively you may achieve same objective using trigger as below:

-- REQUIREMENT 1: WHENEVER WE INSERT DATA INTO 1ST TABLE, AUTO INSERT SAME DATA INTO 2ND TABLE.
CREATE TRIGGER TRIGG1 
ON ReservationTable1
FOR     -- IN ADDITION TO 
INSERT
AS
BEGIN
INSERT INTO ReservationTable2 SELECT * FROM INSERTED 
PRINT 'GIVEN OPERATION IS SUCCESSFUL'
END 

There is another concept in trigger which is called instead of as below:

CREATE TRIGGER Trigg2 
ON ReservationTable1
INSTEAD OF UPDATE, DELETE
AS
    BEGIN
    PRINT 'ABC'
    END  

You may use any of above however temporal table will be preferred choice.

Having said above, all of them will work on individual table level. In case, you want to implement this at whole database level them auditing is the only choice as far as I know, you may choose different events which suits your requirement and enable accordingly. You may read more details from below links:

https://www.sqlshack.com/various-techniques-to-audit-sql-server-databases/

https://www.mssqltips.com/sqlservertip/5291/audit-and-prevent-unwanted-sql-server-table-changes/

I hope above helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.