Rank: Administration
Medals: Groups: Administrators
Joined: 3/22/2010 Posts: 10 Points: 45
Thanks: 0 times Was thanked: 1 time(s) in 1 post(s)
|
-- შევქმნათ ტრიგერი ლოგირებისათვის BB კოდი:SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO
ALTER TRIGGER [dbo].[trglog_students] ON [dbo].[Students] FOR INSERT,UPDATE,DELETE AS -- JUST CHANGE students INTO YOUR OWN TABLENAME TO MAKE IT WORK DECLARE @IUD SMALLINT DECLARE @DEL BIT DECLARE @INS BIT DECLARE @SQLSTRING VARCHAR(2000) SET @DEL = 0 SET @INS = 0 IF EXISTS (SELECT TOP 1 1 FROM DELETED) SET @DEL=1 IF EXISTS (SELECT TOP 1 1 FROM INSERTED) SET @INS = 1 IF @INS = 1 AND @DEL = 0 SET @IUD = 0 IF @INS = 1 AND @DEL = 1 SET @IUD = 1 IF @DEL = 1 AND @INS = 0 SET @IUD = 2 IF @INS = 0 AND @DEL = 0 RETURN
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[Log_students]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1) BEGIN DECLARE @MEMTABLE TABLE ( ID INT IDENTITY ,COLUMNAME SYSNAME ,TYPENAME VARCHAR(20) ) INSERT @MEMTABLE (COLUMNAME,TYPENAME) SELECT NAME,TYPE_NAME(XTYPE) FROM SYSCOLUMNS WHERE ID = OBJECT_ID('[DBO].[students]') --AND TYPE_NAME(XTYPE) <> 'TIMESTAMP' ORDER BY COLID
DECLARE @CUR INTEGER DECLARE @MAX INTEGER DECLARE @SQLSTR AS VARCHAR(8000) DECLARE @CURCOL SYSNAME DECLARE @COLTYPE AS VARCHAR(10) -- SETUP VARIABLES SET @SQLSTR = '' SET @CUR=1 SELECT @MAX = MAX(ID) FROM @MEMTABLE -- LOOP EVEY FIELD WHILE @CUR <= @MAX BEGIN SELECT @CURCOL = COLUMNAME,@COLTYPE = TYPENAME FROM @MEMTABLE WHERE ID = @CUR IF @COLTYPE = 'INT' OR @COLTYPE = 'BIGINT' OR @COLTYPE='UNIQUEIDENTIFIER' SET @SQLSTR = @SQLSTR + ' CAST('+@CURCOL + ' AS '+@COLTYPE+') AS [' + @CURCOL +'] ' ELSE BEGIN IF @COLTYPE = 'TIMESTAMP' SET @SQLSTR = @SQLSTR + ' CAST('+@CURCOL + ' AS varbinary) AS [' + @CURCOL +'] ' ELSE SET @SQLSTR = @SQLSTR + ' '+@CURCOL + ' AS [' + @CURCOL +'] ' END
IF @CUR <= @MAX - 1 SET @SQLSTR=@SQLSTR + ',' SET @CUR = @CUR + 1 END -- ADD THE Log FIELDS SET @SQLSTR = @SQLSTR +',CAST('' '' AS SMALLINT) AS trgiud,CAST(GETDATE() AS DATETIME) AS trgdate,CAST('' '' AS VARCHAR(20)) AS trguser,left(HOST_NAME(),20) AS trghost,left(APP_NAME(),50) AS trgapp' -- SET UP THE SELECT FOR CREATING THE Log TABLE SET @SQLSTR = 'SELECT TOP 0 ' + @SQLSTR + ' INTO [DBO].[Log_students] FROM [DBO].[students]' EXEC(@SQLSTR) SET @SQLSTR = 'ALTER TABLE [DBO].[Log_students] ADD trgid int NOT NULL IDENTITY (1, 1) ; ALTER TABLE [DBO].[Log_students] WITH NOCHECK ADD CONSTRAINT PK_Log_students_trgid PRIMARY KEY CLUSTERED (trgid) WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON) ALTER TABLE [DBO].[Log_students] REBUILD WITH (DATA_COMPRESSION = PAGE);' EXEC(@SQLSTR) DECLARE @value nvarchar(4000) SELECT @value = N'Audit Log - '+convert(nvarchar(4000),value) FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 'students', NULL, NULL); EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@value, @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Log_students' END -- INSERT IF @IUD = 0 INSERT [DBO].[Log_students] SELECT *,0 ,GETDATE(),left(SYSTEM_USER,20),left(HOST_NAME(),20),left(APP_NAME(),50) FROM INSERTED -- UPDATE IF @IUD = 1 INSERT [DBO].[Log_students] SELECT *,1 ,GETDATE(),left(SYSTEM_USER,20),left(HOST_NAME(),20),left(APP_NAME(),50) FROM INSERTED -- DELETE IF @IUD = 2 INSERT [DBO].[Log_students] SELECT *,2 ,GETDATE(),left(SYSTEM_USER,20),left(HOST_NAME(),20),left(APP_NAME(),50) FROM DELETED GO
|