Cannot specify a schema name as a prefix to the trigger name for database and server level triggers sql server

Suppose we have created a table blLoginHistory by using following query:

CREATE TABLE tblLoginHistory(

    ntLoginHistoryID BIGINT PRIMARY KEY IDENTITY,

    vcUserName VARCHAR(100),

    dtDate DATETIME DEFAULT(GETDATE())

)

Now we are creating Logon trigger or DML trigger using following sql queries:

CREATE TRIGGER dbo.trgLoginHistory

ON ALL SERVER

FOR LOGON

AS

BEGIN

    INSERT tblLoginHistory(vcUserName)

    SELECT CURRENT_USER

END

CREATE TRIGGER dbo.trgCreateDatabase

ON ALL SERVER

FOR CREATE_DATABASE

AS

    INSERT tblLoginHistory(vcUserName)

    SELECT CURRENT_USER

We will get error message like :

Cannot specify a schema name as a prefix to the trigger name for database and server level triggers.

Cause: Logon trigger and DDL trigger are sever level objects. So we cannot specify the schema name (dbo in this example).

Solution:

Remove the schema name from trigger name.

CREATE TRIGGER trgLoginHistory

ON ALL SERVER

FOR LOGON

AS

BEGIN

    INSERT tblLoginHistory(vcUserName)

    SELECT CURRENT_USER

END

CREATE TRIGGER trgCreateDatabase

ON ALL SERVER

FOR CREATE_DATABASE

AS

    INSERT tblLoginHistory(vcUserName)

    SELECT CURRENT_USER