Veritabanının programability altında database trigger 'ında bulunmaktadır.
tablo, view, fonksiyon ve procedure lerin değişikliklerini tutmak için kullanılmakadır.
CREATE TABLE [dbo].[ChangeLog](
[LogId] [INT] IDENTITY(1,1) NOT NULL,
[DatabaseName] [VARCHAR](256) NOT NULL,
[EventType] [VARCHAR](50) NOT NULL,
[ObjectName] [VARCHAR](256) NOT NULL,
[ObjectType] [VARCHAR](25) NOT NULL,
[SqlCommand] [VARCHAR](MAX) NOT NULL,
[EventDate] [DATETIME] NOT NULL,
[LoginName] [VARCHAR](256) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[ChangeLog] ADD CONSTRAINT [DF_EventsLog_EventDate]
DEFAULT (GETDATE()) FOR [EventDate]
CREATE TRIGGER [backup_objects]
ON DATABASE
FOR CREATE_PROCEDURE,
ALTER_PROCEDURE,
DROP_PROCEDURE,
CREATE_TABLE,
ALTER_TABLE,
DROP_TABLE,
CREATE_FUNCTION,
ALTER_FUNCTION,
DROP_FUNCTION,
CREATE_VIEW,
ALTER_VIEW,
DROP_VIEW
AS
SET NOCOUNT ON
DECLARE @data XML
SET @data = EVENTDATA()
INSERT INTO changelog(databasename, eventtype,
objectname, objecttype, sqlcommand, loginname)
VALUES(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)
GO
ENABLE TRIGGER [backup_objects] ON DATABASE