Mar 23, 2014

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

No comments:

Post a Comment