Database trigger when a table is created with a particular name

Question

I've reviewed (and tried to use) SQL Server 2008 trigger on create table without success. I currently upload a CSV file to a table called DataTMP. The data in this file is ETL'd into another table using a stored procedure, after which the DataTMP table is dropped.

What I'm looking to achieve is that when DataTMP is created, its creation triggers the execution of the stored procedure and following successful execution, the DataTMP table is dropped.

What I've tried is:

CREATE OR REPLACE TRIGGER trig_DataUpsert 
AFTER CREATE ON SCHEMA
BEGIN
   EXEC usp_data_ETL
   DROP TABLE DataTMP
END

The error is suggested at 'OR' and again 'ON' in the trigger CREATE statement

I'm using SSMS v18

Answer 1

I'm not sure on SQL Server 2008, I don't have an instance to test, but as far as I'm aware, DDL triggers were available back then as well.

Please try this, or adapt to your situation:

--DROP TRIGGER IF EXISTS trig_DataUpsert ON DATABASE
-- alternative for SQL Server 2008, as IF EXISTS wouldn't have been available back then
IF EXISTS (SELECT 1 FROM sys.triggers WHERE [name] = N'trig_DataUpsert' AND [type] = 'TR')
DROP TRIGGER trig_DataUpsert ON DATABASE
GO

CREATE TRIGGER trig_DataUpsert
ON DATABASE
FOR CREATE_TABLE
AS
    DECLARE @tblName NVARCHAR(MAX)
    SELECT @tblName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')

    IF @tblName = N'DataTMP'
    BEGIN
        PRINT 'EXEC usp_data_ETL'
        DROP TABLE DataTMP
    END
    ELSE
        RETURN

Answer 2

If I understood correctly from the linked question, you are trying to use PL/SQL (ORACLE) in SSMS. There is no CREATE OR REPLACE statement in SQL Server. In MSSQL there are only create/alter/drop operations.

The better way to check what is available for triggers in your version of SQL server is to create a trigger from template. In Object Explorer: Your database/Programmability/Database Triggers/right click "New Database Trigger".

The template will look something like this:

USE <database_name, sysname, AdventureWorks>
GO

IF EXISTS(
  SELECT *
    FROM sys.triggers
   WHERE name = N'<trigger_name, sysname, table_alter_drop_safety>'
     AND parent_class_desc = N'DATABASE'
)
    DROP TRIGGER <trigger_name, sysname, table_alter_drop_safety> ON DATABASE
GO

CREATE TRIGGER <trigger_name, sysname, table_alter_drop_safety> ON DATABASE 
    FOR <data_definition_statements, , DROP_TABLE, ALTER_TABLE> 
AS 
IF IS_MEMBER ('db_owner') = 0
BEGIN
   PRINT 'You must ask your DBA to drop or alter tables!' 
   ROLLBACK TRANSACTION
END
GO

Just fill the template and it should work.