SQL Server DDL trigger on drop table

Question

I have a database with few tables: tab1, tab2, tab3. How can I create a DDL trigger on drop table only for tab2 (not for tab1 and tab3). When drop tab2 is called I need to update values in this table but not to delete tab2. How can I do this? I found this but don't understand how it works:

create trigger trDatabse_OnDropTable
on database
for drop_table
as
begin
 set nocount on;
 select
  'Table dropped: ' +
  quotename(eventdata().value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname')) + N'.' +
  quotename(eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'));
end;

Thanks!

Answer 1

In this article DDL Triggers you'll find that there are no INSTEAD OF DDL Triggers, like in DML Triggers, so you can't prevent the drop and execute your own command instead:

DDL triggers fire only after the DDL statements that trigger them are run. DDL triggers cannot be used as INSTEAD OF triggers. DDL triggers do not fire in response to events that affect local or global temporary tables and stored procedures.

This piece of code you posted is for logging DROP TABLE event. eventdata() contains XML with some information on the event: LoginName, ObjectName, TSQLCommand, PostTime and many more.