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