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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.