Skip to main content

Database/DBMS Interview Questions And Answers - Part XX.

82. What Is A Trigger? What Are The Types Of Trigger?

Triggers are database object. Basically these are special type of stored procedure that is automatically fired / executed when a DDL or DML command statement related with the trigger is executed. They can be executed automatically on the insert, delete and update operation.

Trigger allows us to execute a batch of SQL code when an insert, update or delete command is executed against a specific table.

Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.

In SQL Server we can create four types of triggers

  • Data Definition Language (DDL) triggers.
  • Data Manipulation Language (DML) triggers.

      1. After Trigger (using FOR/AFTER CLAUSE)
      2. Instead of Trigger (using INSTEAD OF CLAUSE)

  • Common Language Runtime (CLR) triggers.
  • Logon triggers.

83. What Are Data Definition Language (DDL) Triggers?

DDL trigger can be used to observe and control actions performed on the server, and to audit these operations. DDL triggers can be used to manage administrator tasks such as auditing and regulating database operations.

In SQL Server one can create triggers on DDL statements (like CREATE, ALTER, and DROP) and certain system defined stored procedures that perform DDL like operations.

For Example, If you are going to execute the CREATE LOGIN statement or the sp_addlogin stored procedure to create login user, then both these can execute/fire a DDL trigger that you can create on CREATE_LOGIN event of Sql Server.

84. What Are Data Manipulation Language (DML) Triggers?

In SQL Server one can create triggers on DML statements (like INSERT, UPDATE, and DELETE) and stored procedures that perform DML-like operations.

DML Triggers are of two types:

After Trigger (using FOR/AFTER CLAUSE): This type of trigger fires after SQL Server finishes the execution of the action successfully that fired it.

For Example, If you insert record/row in a table then the trigger related/associated with the insert event on this table will fire only after the row passes all the constraints, like as primary key constraint, and some rules. If the record/row insertion fails, SQL Server will not fire the After Trigger.

Instead of Trigger (using INSTEAD OF CLAUSE): This type of trigger fires before SQL Server starts the execution of the action that fired it. This is different from the AFTER trigger, which fires after the action that caused it to fire.

For Example, If you insert record/row in a table then the trigger related/associated with the insert event on this table will fire before the row passes all the constraints, such as primary key constraint and some rules. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.

85. What Are Common Language Runtime (CLR) Triggers?

CLR triggers are special type of triggers that based on the CLR (Common Language Runtime) in .net framework. CLR integration of triggers has been introduced with SQL Server 2008 and allows for triggers to be coded in one of .NET languages like C#, Visual Basic and F#.

86. What Are Logon Triggers?

Logon triggers are special type of trigger that fire when LOGON event of Sql Server is raised. This event is raised when a user session is being established with Sql Server that is made after the authentication phase finishes, but before the user session is actually established. Hence, all messages that we define in the trigger such as error messages, will be redirected to the SQL Server error log. Logon triggers do not fire if authentication fails.

Logon Triggers can be used to audit and control server sessions, such as to track login activity or limit the number of sessions for a specific login.


Popular posts from this blog

Tell Me Something About Yourself - Interview Answers.

Try to introduce some of your most important employment-oriented skills as well as your education and accomplishments to the interviewer. Answer to this question is very important because it positions you for the rest of the interview. That's why this statement is often called the "Positioning Statement".

One should take the opportunity to show his/her communication skills by speaking clearly and concisely in an organized manner. Since there is no right or wrong answer for this question hence it is important to appear friendly.


1) I am a person with strong interpersonal skills and have the ability to get along well with people. I enjoy challenges and looking for creative solutions to problems.

2) Besides the details given in my resume, I believe in character values, vision and action. I am quick in learning from mistakes. I am confident that the various tests that you have conducted will corroborate my competencies aptitude and right attitude for the…

100 C Programs Frequently Asked in Technical Interview.

Why Should We Hire You? - Interview Answers

The interviewer asks this question just to find out how your skills, experience and knowledge can add value to the job.Well to answer this question you need to learn what they are looking for and then tie them with your strengths. This ensures that you are a viable fit for the position.

The more detail you give the stronger your answers will be. Try to summarize all your accomplishments and relate what makes you unique.

The question can also be

"Why should we hire you over other candidates?"
"Why should we hire you over the others waiting to be interviewed?"

Your answers can be:

1) You should hire me because I’m the best person for the job. I realize that there are likely other candidates who also have the ability to do this job. Yet I bring an additional quality that makes me the best person for the job - my passion for excellence. I am passionately committed to producing truly class results.

2) I believe I’m the perfect fit for this position. I’m very detai…