DDL Trigger Error

Question

DDL Trigger Error

amy26

SSCrazy Eights

Points: 8926
More actions
November 20, 2015 at 11:33 am

#399360

So I found some code here on SQL Server Central to prevent people from dropping tables in the database. However, I am getting an error when I try and create the trigger:
XQuery [value()]: There is no function '{http://www.w3.org/2004/07/xpath-functions}:DATA()'
The code it is bombing on is:
SELECT
@EDATE=GETDATE(),
@UNAME=@EVENTDATA.value('DATA(/EVENT_INSTANCE/USERNAME)[1]', 'SYSNAME'),
@ONAME=@EVENTDATA.value('DATA(/EVENT_INSTANCE/OBJECTNAME)[1]', 'SYSNAME'),
@OTEXT=@EVENTDATA.value('DATA(/EVENT_INSTANCE/TSQLCOMMAND/COMMANDTEXT)[1]',
'VARCHAR(MAX)'),
@ETYPE=@EVENTDATA.value('DATA(/EVENT_INSTANCE/EVENTTYPE)[1]', 'NVARCHAR(100)')
Do I not have something installed properly?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Can you post the entire code?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

amy26 SSCrazy Eights Points: 8926 More actions · Answer 2

create table TBL_AUDIT_PROTECTEDTABLES(TABLENAME varchar(200) NOT NULL);

create table TBL_AUDIT_DROP_ERRORLOG([ID] [int] IDENTITY(1,1) NOT NULL,EVENT_DATE DATETIME,USERNAME NVARCHAR(50),OBJECTNAME NVARCHAR(100), COMMAND_TEXT NVARCHAR(MAX),EVENT_TYPE NVARCHAR(100));

GO

CREATE TRIGGER [TR_PROTECT_TABLES]

ON DATABASE

FOR

DROP_TABLE

AS

BEGIN

DECLARE @EVENTDATA XML,

@UNAME NVARCHAR(50),

@ONAME NVARCHAR(100),

@OTEXT VARCHAR(MAX),

@ETYPE NVARCHAR(100),

@EDATE DATETIME

SET @EVENTDATA = EVENTDATA()

SELECT

@EDATE=GETDATE(),

@UNAME=@EVENTDATA.value('DATA(/EVENT_INSTANCE/USERNAME)[1]', 'SYSNAME'),

@ONAME=@EVENTDATA.value('DATA(/EVENT_INSTANCE/OBJECTNAME)[1]', 'SYSNAME'),

@OTEXT=@EVENTDATA.value('DATA(/EVENT_INSTANCE/TSQLCOMMAND/COMMANDTEXT)[1]',

'VARCHAR(MAX)'),

@ETYPE=@EVENTDATA.value('DATA(/EVENT_INSTANCE/EVENTTYPE)[1]', 'NVARCHAR(100)')

IF @ONAME IN (SELECT TABLENAME FROM PROTECTEDTABLES)

BEGIN

DECLARE @ERR VARCHAR(100)

SET @ERR = 'TABLE ' + @ONAME + ' IS PROTECTED AND CANNOT BE DROPPED.'

RAISERROR (@ERR, 16, 1) ;

INSERT INTO TBL_AUDIT_DROP_ERRORLOG (EVENT_DATE,USERNAME,OBJECTNAME, COMMAND_TEXT,EVENT_TYPE)

VALUES (@EDATE,@UNAME,@ONAME,@OTEXT,@ETYPE)

ROLLBACK;

END

GO

Lowell SSC Guru Points: 323518 More actions · Answer 3

the core error is that xml is case sensitive, but you capitalized some of the values.

SELECT

@edate=GETDATE(),

@uname=@eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),

@oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),

@otext=@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',

'VARCHAR(MAX)'),

@etype=@eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')

one other things i'd change:

you didn't post the PROTECTEDTABLES table, you have to GRANT SELECT TO PUBLIC ON PROTECTEDTABLES to make sure the trigger runs without an error.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

amy26 SSCrazy Eights Points: 8926 More actions · Answer 4

Oh that would explain it. Didn't know it was case sensitive. Thanks! I like all my code to be capitalized... 🙂

And yup, I know I need to grant permission on that table.

Thank you!

amy26 SSCrazy Eights Points: 8926 More actions · Answer 5

One more question - I got the procedure to work. And if I try to execute a DROP command I get the error message but its still allowing the drop to happen. 🙁 Why isn't it preventing it? 🙁

Lowell SSC Guru Points: 323518 More actions · Answer 6

Could you be raising an error but left off the rollback tran command?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

amy26 SSCrazy Eights Points: 8926 More actions · Answer 7

No there is a rollback in the code. I would think that it would need to be captured in an actual transaction for the rollback to actually work though?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

All modification statements are part of a transaction and any trigger fires within that transaction.

If the transaction is committing, then it's because a rollback isn't running. Maybe the raiserror is transfering control elsewhere and terminating the trigger before the roll back runs

There's another problem with your trigger, you insert into the logging table right before the rollback, so the rollback will roll that back too.

I'd do

ROLLBACK

INSERT

RAISERROR

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass