asked
Alert on table create
Hi Guys, I want to create an alert/trigger on 'create table/Select * into' and want to record it into a maintenance log table. Development users have rights on database and can create table by any of below command a. Select * into newtable from oldtable b. Create Table Tablename So i want to trace it into a maintenance log table with below information. Since user unnecessarily create new tables every time and it increase the database size and blame on others. TableName/Object Name | CreateDateTime | CreatedBy | Schema | DatabaseName | IP/Hostname Please if any one can suggest and provide the solution on it i tried through trigger but not able to do.
You can do it with a database level ddl-trigger. use GO CREATE TRIGGER trg_ddl_create_table ON DATABASE FOR CREATE_TABLE AS PRINT 'Table created'; In order to do something more useful than just a PRINT, you ned to look at the XML-structure of Eventdata related to a DDL-trigger. It will require some xpath-querying against an XML-datatype. Here's some documentation about EVENTDATA, along with a few examples:
https://docs.microsoft.com/en-us/sql/t-sql/functions/eventdata-transact-sql
17 People are following this question.
