Database trigger

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

Bu makaleyi yararlı buldunuz mu?