Thursday, November 18, 2010

Simple questions on DML and DDL Triggers

Triggers contain code that automatically runs in response to events within the database. Triggers cannot be called or executed manually. DDL trigger is a new feature in SQL Server 2005. You expect questions on this for your certification exams.
Questions:
1.       Which of the following statements are valid for using inside a trigger?
A.      INSERT
B.      ALTER DATABASE
C.      RESTORE DATABASE
D.      CREATE INDEX 
2.       You are supposed to validate data entry and return error message for invalid data. How will you achieve this with minimum effort?
A.      Implement a DML trigger
B.      Include check constraints
C.      Implement DDL trigger
D.      Create a CLR function
3.       You need to import data to a table. If some rows are failed to import due to data validation.  Then you decide to create new table to collect the failed rows. How will you achieve this?

A.      Find the failed rows using a T-SQL query and insert into new table.
B.      Create a DML INSTEAD OF trigger to insert the failed rows to new table.
C.      Create a DML AFTER trigger to insert the failed rows to new table.
D.      Create a DDL AFTER trigger to insert the failed rows to new table.
4.       You are supposed to track who drops the table, procedures, views, databases. What will you do for that?
             A.      Check the application event log.
             B.      Create a DDL trigger.
             C.      Create a DML trigger.
             D.      Use System Stored Procedures.
 
5.       What type of trigger is the following code represents?
CREATE TRIGGER trgTest
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT 'Tables cannot be dropped'
ROLLBACK;
A.      DML AFTER trigger.
B.      DML INSTEAD OF trigger.
C.      DDL trigger – Server scoped.
D.      DDL trigger-Database Scoped.

Show Answers:

3 comments:

  1. Can explain diffrence between DDl and DML

    Narayanan.T

    ReplyDelete
  2. * A DML trigger executes when a data manipulation language (DML) event occurs in the database server.
    DML events include UPDATE, INSERT, or DELETE statements issued against a table or view.
    DML triggers are used to enforce business rules when data is modified and to extend the integrity checking logic of constraints, defaults, and rules.

    * DDL triggers fire in response to a variety of Data Definition Language (DDL) statements.
    These statements are primarily statements that start with CREATE, ALTER, and DROP.
    DDL triggers can be used for administrative tasks such as auditing and regulating database operations.

    ReplyDelete