SQL: track changes in a specific sql table.

Hello experts,
I would like to track the various changes performed in a specific table.
Is there a transaction in a SQL or a query in order to have a result of the various actions performed in a table?
Thank you very much for your help.
Regards,
LVL 1
LD16Asked:
Who is Participating?
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
Ste5an's is a DDL and only covers schema changes (DDL = Data Definition Language).

You will need a trigger for DML = Data Manipulation Language or data changes.
The below template will help you see.
CREATE TRIGGER tr_tablename_i on dbo.tablename
AFTER INSERT
AS
INSERT INTO dbo.audittable_tablename (columns here)
SELECT columns, 'I' as action FROM inserted

GO

Open in new window


Documentation for CREATE TRIGGER is here https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql.
0
 
ste5anSenior DeveloperCommented:
When fully logged, then you can query the transaction log. Otherwise you can query the default trace. Both options can be not available.

Thus: What is your use-case?

You can use SQL Server Auditing or SQL Server CDC or do your own based on triggers.
0
 
LD16Author Commented:
Hello,
Reference table is delete by an unknown service between specific date we want to track activities performed related to the table.
How to implement trigget concerning an specific table?

Thank you very much for your help.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
ste5anSenior DeveloperCommented:
This would be a database-level (DDL) Trigger querying the extendend events. E.g.

USE tempdb;
GO

CREATE TABLE yourCustomLogTable
    (
        schema_name sysname ,
        table_name sysname ,
        application_name sysname ,
        user_name sysname ,
        log_date DATETIME
            DEFAULT ( GETDATE())
    );
GO

CREATE TRIGGER tr_TableDropped
ON DATABASE
FOR DROP_TABLE
AS
SET NOCOUNT ON;

INSERT INTO yourCustomLogTable ( schema_name ,
                                 table_name ,
                                 application_name ,
                                 user_name )
            SELECT EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname') ,
                   EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname') ,
                   APP_NAME() ,
                   CURRENT_USER;
GO

CREATE TABLE test1
    (
        id INT
    );
GO

DROP TABLE test1;
GO

SELECT *
FROM   dbo.yourCustomLogTable YCLT;
GO

DROP TRIGGER tr_TableDropped
    ON DATABASE;
GO

DROP TABLE dbo.yourCustomLogTable;
GO

Open in new window

0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
Are you looking for changes to the Data in the table or changes to the structure of the Table?

For the Structure you would use a DDL trigger as was said above. If you are looking for the Data Changes, then you would need a DML trigger.  (INSERT, UPDATE, DELETE) triggers would be created for the table.
0
 
LD16Author Commented:
Hello,

Just to monitor data change.

@Ste5an: proposed query works with SQL management server 2012?

Thank you very much for your help.
0
 
ste5anSenior DeveloperCommented:
Just to monitor data Change.
So it's not to monitor  "Reference table is delete" which means table dropped to me, but its "row is deleted"? Then it's:

USE tempdb;
GO

CREATE TABLE Payload
    (
        ID INT IDENTITY NOT NULL PRIMARY KEY ,
        PayloadText NVARCHAR(255) NOT NULL
    );
GO

CREATE TABLE PayloadHistory
    (
        ID INT NOT NULL ,
        Payload NVARCHAR(255) NOT NULL ,
        application_name sysname ,
        user_name sysname ,
        log_date DATETIME
            DEFAULT ( GETDATE())
    );
GO

CREATE TRIGGER tr_RowDeleted
ON dbo.Payload
AFTER DELETE
AS
SET NOCOUNT ON;
INSERT INTO dbo.PayloadHistory ( ID ,
                                 Payload ,
                                 application_name ,
                                 user_name )
            SELECT D.ID ,
                   D.PayloadText ,
                   APP_NAME() ,
                   CURRENT_USER
            FROM   Deleted D;
GO

INSERT INTO dbo.Payload ( PayloadText )
VALUES ( N'Test1' ) ,
       ( N'Test2' );

DELETE FROM Payload
WHERE PayloadText LIKE 'Test%';

SELECT *
FROM   dbo.PayloadHistory PH;
GO

DROP TABLE dbo.Payload;
DROP TABLE dbo.PayloadHistory;
GO

Open in new window

proposed query works with SQL management server 2012?
Test it.
0
 
LD16Author Commented:
Hello,
Thank you very much for your proposal.
I am not an expert in sql.
Based on both proposal I would like to know how should I build queries.
my databasename: rib
my table to track: lid3

Can you help me to build queries to monitor delete and insert changes in lid3 table?

Thank you very much for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.