CODEWITHSUNDEEP
sqlsql-servert-sqltriggers
Manel Hkiri
Manel Hkiri

Reputation: 115

How to prevent the trigger from sending an error if the table does not exist?

I wrote trigger after deleting a row (user) to drop the table that has the name composed from ID of this row (but not every ID has a table), I don't like to throw exception in code if the table does not exist!

CREATE TRIGGER AfterDeleteUser
            ON usersProject
            FOR DELETE
            AS DECLARE @IDres VARCHAR(50),
            @tablename VARCHAR(50)


            SELECT @IDres = ins.IDressource FROM DELETED ins;
            set @tablename = concat('MSG_', @IDres);
            
            SET NOCOUNT ON;
            DECLARE @Sql NVARCHAR(MAX);

            SET @Sql = N'DROP TABLE '+QUOTENAME(@tablename)
        
                EXECUTE sp_executesql @Sql;
            go

I get this error when the table does not exist!

Msg 3701, Niveau 11, État 5, Ligne 7

Cannot drop the table 'MSG_', because it does not exist or you do not have permission.

Upvotes: 0

Views: 356

Answers (1)

Esteban P.
Esteban P.

Reputation: 2809

From SQL Server 2016 on, you could use the syntax:

DROP TABLE IF EXISTS [Tablename]

For older versions you could do:

IF OBJECT_ID('tablename', 'U') IS NOT NULL 
  DROP TABLE tablename;

Upvotes: 3

Related Questions