Created
March 25, 2019 13:09
-
-
Save hakanak/cfc7ab9b4f37b4231ccb7a8277be7a4a to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--======================== Değişiklikleri tutacak tablo ======================= | |
CREATE TABLE [dbo].[ChangeLog]( | |
[LogId] [int] IDENTITY(1,1) NOT NULL, | |
[DatabaseName] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, | |
[EventType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, | |
[ObjectName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, | |
[ObjectType] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, | |
[SqlCommand] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, | |
[EventDate] [datetime] NOT NULL CONSTRAINT [DF_EventsLog_EventDate] DEFAULT (getdate()), | |
[LoginName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL | |
) ON [PRIMARY] | |
go | |
--============= Değişiklikleri ChangeLog tablosuna ekleyecek trigger =========== | |
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 | |
as | |
set nocount on | |
declare @data xml | |
DECLARE @client_ip VARCHAR(15) | |
set @data = EVENTDATA() | |
SELECT @client_ip = client_net_address | |
FROM sys.dm_exec_connections | |
WHERE session_id =@data.value('(/EVENT_INSTANCE/SPID)[1]', 'varchar(256)') | |
insert into YOURDATABASE.dbo.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/SchemaName)[1]', 'varchar(256)') +'.'+ | |
@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)')+'('+@client_ip+')' | |
) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment