Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

SQL Server 2005 – Using DDL Triggers

By Exforsys | on December 12, 2005 |
SQL Server 2005

SQL Server 2005 – Using DDL Triggers

In this tutorial you will learn about DDL Triggers vs DML Triggers, Syntax for creating a DDL Trigger, Designing a DDL Trigger, The EVENTDATA() Function, Implementing a Database-Scoped DDL Trigger, Implementing a Server-Scoped DDL Trigger, Event Notifications vs. DDL Triggers and General Trigger Considerations.

DDL triggers fire stored procedures in response to DDL Statements which begin with CREATE, ALTER and DROP. The DDL triggers require that the DDL statements that trigger them are run and these triggers cannot be used as INSTEAD OF triggers. The DDL events that fire the DDL triggers have to be specified in T-SQL DDL syntax. System stored procedures that perform DDL like operations are not supported. The scope of the trigger is confined to the current database or current server and the scope depends on the T-SQL event being processed. These triggers can be used for Administrative tasks such as auditing and regulating database operations. DDL triggers can be used to perform the following operations

1. Prevent changes to Database schema
2. Fire events in response to changes in the database schema
3. Record changes or events in the database schema

DDL Triggers vs DML Triggers

While DML triggers operate on INSERT, UPDATE and DELETE statements to enforce business rules and data integrity checks, DDL Triggers operate on CREATE, ALTER, DROP and other DDL statements and enforce business rules that affect databases across servers. Unlike DML triggers, DDL triggers are not scoped to schemas. However, both the types of triggers are created, modified and dropped using similar T_SQL syntax. They run managed code packaged in an assembly created in the Microsoft .NET framework and uploaded in the SQL server. Moreover, like DML triggers, DDL triggers can create and use more than one trigger on the same T_SQL Statement. The DDL trigger and the statement that fires the trigger are run within the same transaction. This transaction can be rolled back from within the trigger when there is an error. If a DDL trigger is run from a batch and explicitly includes the ROLLBACK TRANSACTION statement it will cancel the whole batch. Finally both DDL and DML triggers can be nested.

Syntax for creating a DDL Trigger

Designing a DDL Trigger

Before designing a DDL trigger the Administrator must define the scope of the trigger. He must decide whether he wants the trigger to execute at the database or server level. He must also decide whether the trigger will respond to a single DDL statement or a group of related statements. Once these have been decided upon the DBA can go on to use the EVENTDATA() TSQL function for coding the response.

The EVENTDATA() Function

 

This function returns XML data with event time, system process ID and event type details. The EVENTDATA() function then used by the DDL trigger to determine the kind of response to the event. The DBA can then use the XQuery to retrieve information from XML data.

The following is a sample of XML data returned by the EVENTDATA() function:

The XML returned by the function will be accessed using the following code.

If single values have to be retrieved using EVENTDATA() then the query statement wil have to be used.

Implementing a Database-Scoped DDL Trigger

Database scoped DDL triggers are fired whenever events modifying database schema are executed. The CREATE TRIGGER ON DATABASE directive is used to scope the database scoped trigger. These triggers are stored within the database and fires on events with the exception of those relating to temporary tables.

Implementing a Server-Scoped DDL Trigger

A limited set of server changes trigger off Server scoped DDL Triggers. The CREATE TRIGGER ON ALL SERVER directive scopes the DDL trigger to the server. These are stored in the master database as objects. The events are related to database changes such as CREATE, ALTER, DROP or security level changes such as CREATE LOGIN, ALTER LOGIN or DROP LOGIN.

Event Notifications vs. DDL Triggers

DDL Triggers and Event Notifications both provide login mechanisms. However, the Event notification is an asynchronous event and requires the SQL Server 2005 service Broker for processing. The event notification can be located on another server and still be processed by the current instance issuing the notification. The Event notifications can also respond to trace events. Like DDL Triggers, Event notifications utilize similarly formatted xml but do not use the EVENTDATA() function.

DDL Triggers on the other hand use the EVENTDATA() function and run in the scope of the transaction and hence a transaction can be rolled back in a DDL trigger. This is not so in an Event notification.

General Trigger Considerations

As stated earlier multiple triggers can be created for each DML or DDL statement. In SQL Server 2005 if a CREATE TRIGGER FOR UPDATA is executed on a table that already has an UPDATE Trigger, an additional trigger can be created.

Recursive Triggers enable recursions to occur when the RECURSIVE TRIGGER setting is enabled using the ALTER DATABASE. Indirect recursion occurs when cascading triggers are fired and tables are updated. Direct recursion occurs when the trigger is fired to update a table directly.

When the RECURSIVE_TRIGGERS setting is disabled, direct recursions are prevented. Indirect Recursion can be disabled by setting the nested triggers server option to 0 by using sp_configure. If any one trigger is set to perform a rollback transaction all triggers will cease to be executed.

Triggers can be nested up to 32 levels maximum. If two or more triggers are created for a table, the firing of one trigger will lead to the cascading firing of other triggers. If any one trigger in the chain goes into a loop the trigger is cancelled. Nested triggers can be disabled using the sp_configure to 0. When nested triggers are set to 0, recursive triggers are also disabled.

Triggers share with stored procedures and batches, the capability of referring to tables that do not exist at compile time. This ability is called the deferred name resolution. 

« « SQL Server Monitoring Tools – Server Profiler
Servlets Advanced » »

Author Description

Avatar

Editorial Team at Exforsys is a team of IT Consulting and Training team led by Chandra Vennapoosa.

Free Training

RSSSubscribe 0 Followers
  • Popular
  • Recent
  • SQL Server 2005 – Upgrading from earlier versions of SQL Server

    December 7, 2005 - 0 Comment
  • Data Manipulation Language (DML) in SQL Server 2005

    October 15, 2005 - 0 Comment
  • SQL Server 2005 – Defining Indexes

    December 20, 2005 - 0 Comment
  • SQL Server 2005 – Server Groups

    November 19, 2005 - 0 Comment
  • SQL Server 2005 – Mirror Server

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Testing Troubleshooting

    December 12, 2005 - 0 Comment
  • SQL Server 2005 – Service Broker

    October 17, 2005 - 0 Comment
  • Maintain indexes in a SQL Server 2005 database

    December 20, 2005 - 0 Comment
  • SQL Server 2005 – Management studio interface Summary Page

    November 23, 2005 - 0 Comment
  • SQL Server 2005 Replication Enhancements

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Configuring Replication

    January 11, 2006 - 0 Comment
  • SQL Server 2005 Replication Enhancements

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Mirror Server

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Introduction to Data Availability

    January 1, 2006 - 0 Comment
  • SQL Server 2005 – Backing up a Database

    January 1, 2006 - 0 Comment
  • SQL Server 2005 – Using Database Snapshots

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Disaster Recovery

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Managing Certificates

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Managing Permissions

    December 26, 2005 - 0 Comment
  • Managing SQL Server 2005 Security

    December 24, 2005 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • SQL Server 2005 – Configuring Replication
  • SQL Server 2005 Replication Enhancements
  • SQL Server 2005 – Mirror Server
  • SQL Server 2005 – Introduction to Data Availability
  • SQL Server 2005 – Backing up a Database

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT