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).