DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: General
       *****************************************************
       #Post#: 29--------------------------------------------------
       DDL and Logon Triggers 
       By: srinivasma_exceldbp Date: March 21, 2014, 4:22 am
       ---------------------------------------------------------
       /*
       DDL Triggers to capture events in the database
       drop trigger ddltrigger_altertable on database
       drop trigger ddltrigger_drop_table on database
       drop table dbo.ddl_table_log
       drop table dbo.ddltable_test
       */
       ------------------------------------------------
       -- 1. DDL Trigger to capture alter table events
       ------------------------------------------------
       -- create a table to capture alter table events
       create table dbo.ddl_table_log
       (
       eventtime datetime,
       eventowner nvarchar(100),
       eventtsql nvarchar(4000)
       )
       go
       --------------------------------------------------
       /*
       create the ddl trigger for alter table
       Note; XML is case sesnitive
       So /EVENT_INSTANCE/TSQLCommand/CommandText)[1]
       should be provided as it is
       If you change the case it will not work
       */
       create trigger ddltrigger_altertable
       on database for alter_table
       as
       declare @data xml
       set @data = EVENTDATA()
       insert into dbo.ddl_table_log
       values
       (
       GETDATE(),
       CURRENT_USER,
       @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvar
       char(4000)')
       )
       --------------------------------------------------
       -- Now, create a table to test alter table command to log
       create table dbo.ddltable_test
       (
       orgid int identity(1,1) primary key ,
       orgname varchar(20) not null
       )
       go
       -- Insert a record
       insert into dbo.ddltable_test(orgname)
       values ('DivaInfotech')
       go
       select * from dbo.ddltable_test
       go
       /*
       Now there is a new organization
       which requires column width to be increased
       increase orgname from varchar(20) to varchar(80)
       */
       alter table dbo.ddltable_test
       alter column orgname varchar(80) not null
       go
       -- check alter table commad is logged into ddl_table_log
       select * from dbo.ddl_table_log
       go
       -- insert orgname having value greater than 20
       insert into dbo.ddltable_test(orgname)
       values ('Indian Oil and Natural Gas Corporation Limited')
       go
       -----------------------------------------------
       /*
       In a business enviornment, it may be required
       that even system administrator should not drop
       or truncate tables by accident.
       */
       ------------------------------------------------
       -- 2. DDL Trigger to prevent drop table
       ------------------------------------------------
       create trigger ddltrigger_drop_table
       on database for drop_table
       as
       print ' You can not drop and truncate the table'
       rollback
       ------------------------------------------------
       -- Let us try to drop the table
       drop table dbo.ddl_table_log
       go
       ------------------------------------------------
       /*
       Logon triggers
       Logon triggers can be used to record information
       of the login user or prevent a particular user
       to logon during business hours
       */
       create trigger checklogontrigger
       on all server for logon
       as
       declare @currenttime datetime = getdate()
       if ORIGINAL_LOGIN() = 'maintuser'
       and @currenttime between '09:00' and '18:00'
       begin
       print ( 'Maintenance users can not login in business hours')
       rollback
       end
       *****************************************************