vk-kirov (5/27/2010)

---

webrunner (5/27/2010)

---

The invalid object error is in there, but the SELECT statements agree with the SELECT statement results presented in the QOTD correct answer. What statement does the invalid object error correspond to - the INSERT statement in the trigger?

Exactly. When SQL Server executes the INSERT statement, the table 'test_logs' doesn't exists because it's been dropped by the DROP TABLE statement. You may modify the trigger as follows:

CREATE TRIGGER test_ddl

ON DATABASE

FOR DDL_TABLE_EVENTS

AS

SET NOCOUNT ON

SELECT * FROM sys.tables WHERE name = 'test_logs'

INSERT test_logs (log_message) VALUES ('test_ddl trigger fired')

GO

This trigger returns 1 row when the CREATE TABLE statement is executed, and 0 rows when the DROP TABLE statement is executed (which means the table 'test_logs' doesn't exist).