0

I know simple ddl triggers like Create_Table, Alter_Table, Drop_Table, I worked with this.

Now I want to know about something like: when a user detaches the database, a trigger should be fired whether the user is valid or not.

Create Trigger trgNoNewTables
ON Database
For Create_Table
AS
BEGIN
      Print 'No Tables Please'
      ROLLBACK
END

May I know like the above trigger there is any trigger for detach and attach?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Pallavi
  • 87
  • 1
  • 13

1 Answers1

1

You can't.

At best you can create trigger(s) for create/alter/drop database:

create trigger foo
on all server
for create_database, drop_database, alter_database
as
print 'triggered!'
go

but sp_detach_db will not fire it (sp_attach_db will).

The proper approach to restrict users from performing actions is security (grant/deny/revoke). Nothing else will work. I suggest you do not grant permissions if users are not allowed to perform an action.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569