From the course: Complete Guide to Advanced SQL Server
Join today to access over 24,400 courses taught by industry experts.
Reading the EVENTDATA() XML - SQL Server Tutorial
From the course: Complete Guide to Advanced SQL Server
Reading the EVENTDATA() XML
DDL triggers aren't fired by data modification events, so it may seem obvious that they don't use the same Inserted and Deleted tables like their DML counterparts. But what DDL triggers do have access to is the full list of events and parameters that were sent to the server that caused the trigger to fire. When writing a trigger, you can access this information using a SQL Server function called EVENTDATA. The EVENTDATA function returns data as XML text, so you'll have to dig into the EVENTDATA XML schema in order to make use of it. You can find the full schema description at this URL. Now, this is an extremely long document, so you'll want to use your browser's FIND IN PAGE function to quickly jump to the section that you want to review. So let's run through an example. I want to find all the information that's available about any CREATE_TABLE events that execute on my database. Do a search through the XML schema for the DDL event that you want your trigger to fire on. In my case…
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Download courses and learn on the go
Watch courses on your mobile device without an internet connection. Download courses using your iOS or Android LinkedIn Learning app.
