Trigger Interview Questions & Answers

  1. Question 1. Explain Trigger And Trigger Types?

    Answer :

    A trigger is a special kind of a store procedure that executes in response to certain action performed on the table like insertion, deletion or updating of data

    Basically there are two logical types:

    • DDL triggers
    • DML triggers

    DDL triggers you can categorize:

    • Transact-SQL DDL Trigger
    • CLR DDL Trigger

    DML trigger types are:

    • INSTEAD OF DML trigger
    • AFTER DML trigger

    There is one special type trigger called logon trigger in sql server.

  2. Question 2. What Are Ddl Triggers And Types Of Ddl Trigger?

    Answer :

    DDL triggers fire in response to an event happening on the server. However, DDL triggers do not fire in response to UPDATE, INSERT, or DELETE (DML) statements on a table or view. Instead, they fire in response to Data Definition Language (DDL) statements that start with the keywords CREATE, ALTER, and DROP.

    DDL triggers you can categorize:

    • Transact-SQL DDL Trigger
    • CLR DDL Trigger
  3. PL/SQL Interview Questions

  4. Question 3. What Are Dml Triggers And Types Of Dml Triggers?

    Answer :

    DDL triggers fire in response to UPDATE, INSERT, or DELETE (DML) statements on a table or view.

    DML trigger types are:

    • INSTEAD OF DML trigger
    • AFTER DML trigger
  5. Question 4. What Is Transact-sql Ddl Trigger?

    Answer :

    A special type of Transact-SQL stored procedure that executes one or more Transact-SQL statements in response to a server-scoped or database-scoped event. For example, a DDL Trigger may fire if a statement such as ALTER SERVER CONFIGURATION is executed or if a table is deleted by using DROP TABLE.

  6. SQL Server 2008 Tutorial

  7. Question 5. What Is Clr Ddl Trigger?

    Answer :

    Instead of executing a Transact-SQL stored procedure, a CLR trigger executes one or more methods written in managed code that are members of an assembly created in the .NET Framework and uploaded in SQL Server.

    Example:

    CREATE TRIGGER safety

    ON DATABASE

    FOR DROP_TABLE, ALTER_TABLE

    AS

       PRINT ‘You must disable Trigger “safety” to drop or

    alter tables!’

       ROLLBACK;

  8. SQL Server 2008 Interview Questions

  9. Question 6. What Is After Dml Trigger?

    Answer :

    AFTER triggers are executed after the action of the INSERT, UPDATE, MERGE, or DELETE statement is performed successfully.

  10. Question 7. What Is Instead Of Dml Trigger?

    Answer :

    DML trigger is executed instead of the triggering SQL statement, therefore, overriding the actions of the triggering statements. Therefore, they can be used to perform error or value checking on one or more columns and the perform additional actions before insert, updating or deleting the row or rows.

  11. SQL Database Tutorial
    SQL Database Interview Questions

  12. Question 8. What Is Logon Trigger?

    Answer :

    Special type of trigger fire when LOGON event of Sql Server is raised is called lo logon trigger. You can use this trigger to audit Sql server activities, control server sessions, such as to track login activity or limit the number of sessions for a specific login.

  13. Question 9. Why The Trigger Fires Multiple Times In Single Login?

    Answer :

    Reason multiple SQL Server services are running as well as intelligence is turned on.

  14. PostgreSQL Interview Questions

  15. Question 10. It Is Possible To Create Trigger On Views?

    Answer :

    Yes, only Instead Of trigger.

  16. Apex Tutorial

  17. Question 11. How Many Triggers Are Possible Per Table?

    Answer :

    One Instead Of Trigger and any After Triggers.

  18. SQL DBA Interview Questions

  19. Question 12. When Multiple After Triggers Are Attached To Sql Table, How To Control The Order Of Execution?

    Answer :

    Using sp_settriggerorder procedure.

  20. PL/SQL Interview Questions

  21. Question 13. What Is The Difference Between For Trigger And After Trigger?

    Answer :

    It’s same.

  22. Spark SQL Programming Tutorial

  23. Question 14. What Are The Types Of Triggers?

    Answer :

    It’s Instead Of trigger and For Trigger.

  24. Question 15. What Is The Difference Between Instead Of And After/for Trigger?

    Answer :

    Instead Of trigger will be fired on behalf of DML statements whereas After trigger will be fired after DML statements.

  25. SQL Interview Questions

  26. Question 16. Is It Possible To Create Trigger On Views?

    Answer :

    Yes, only Instead Of trigger.