IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Students]') AND type in (N'U'))
DROP TABLE [dbo].[Students]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Students](
    [studentID] [int] IDENTITY(1,1) NOT NULL,
    [FName] [nvarchar](50) NOT NULL,
    [LName] [nvarchar](50) NOT NULL,
    [PersonalNumber] [nvarchar](11) NULL,
    [Bdate] [date] NULL,
CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED
(
    [studentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

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