I want to use the database level DDL trigger for logging actions in my DB. I need to get table name and action (insert, update, delete) and write it in table with logs.
Can I get table name in database level trigger and use it to insert table name? Or need to put triggers on all the tables?
Here's a setup that I use on many of my databases. It demonstrates most of the things that you asked about:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Meta].[DdlEvents](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CreatedOn] [datetime] NULL,
[CreatedBy] [sysname] NULL,
[CreateBy2] [sysname] NULL,
[SchemaName] [sysname] NULL,
[ObjectName] [sysname] NULL,
[HostName] [sysname] NULL,
[ProgramName] [sysname] NULL,
[SqlCommand] [nvarchar](max) NULL,
[XmlData] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TRIGGER [DDLTrigger_LogDDL]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@EventData XML = EVENTDATA();
INSERT INTO Meta.DdlEvents(
SqlCommand,
SchemaName,
ObjectName,
HostName,
ProgramName,
XmlData
)
VALUES (
@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'),
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),
HOST_NAME(),
PROGRAM_NAME(),
@EventData
);
END
GO
ENABLE TRIGGER [DDLTrigger_LogDDL] ON DATABASE
GO
Specifically, you use the EVENTDATA() function within the Database DDL trigger to get the Event XML, then you can extract the object(table's) object and schema at the /EVENT_INSTANCE/SchemaName and /EVENT_INSTANCE/ObjectName nodes.
Follow-up on Martin's comment below for the OP, a DDL triger only fires on DDL events, that is commands like CREATE,ALTER and DROP. It does not fire for DML events like INSERT, UPDATE and DELETE. So, if that is what you need, then this answer would not work, and yes, you would need a trigger on every table.
