Top 22 SQL Server Trigger Interview Question with Answers
Hi SQL developers, here we come with one of the most important topic of SQL Server which is know as Triggers, Here will have almost all Trigger related interview questions, This set contain most FAQ trigger related interview questions.
TOP 22 SQL SERVER TRIGGER INTERVIEW QUESTIONS ANSWER
1). Explain what is a Triggers in SQL Server?
Ans: Click here for answers
2). What are the type of Triggers?
Ans: There are two types of triggers
Ans: After Triggers
These triggers run after an insert, update or delete on a table. They are not supported for views.
AFTER TRIGGERS can be classified further into three types as:
Ans: 32 levels
5). What are magic tables in SQL Server?
Ans: Magic tables are nothing but inserted and deleted which are temporary object created by server internally to hold the recently inserted values in the case of insert and to hold recently deleted values in the case of delete, to hold before updating values or after updating values in the case of update.
6). How many types of Magic tables available in SQL server?
Ans: There are two types of Magic tables available in SQL server:
Inserted
Deleted
7). Suppose you have a view that is formed by joining two tables together, what database object would allow you to insert a new row into both of the two tables joined together?
Ans: An INSTEAD OF trigger
8). If a trigger aborts due to a run-time error and no exception handler exists, what will occur?
Ans: The transaction will be rolled back and control passes to the calling environment.
9). Which T-SQL statement can you include in the code of the trigger to indicate a normal exit from the trigger?
Ans: RETURN
10). If there is an AFTER INSERT trigger on a table, how many times will that trigger fire if you insert 50 rows using a single INSERT?
Ans: Trigger will fire once
11). If a DML trigger fires and executes another DML statement also contains a trigger, what will happen to that second trigger, it will fire or not?
Ans: The second trigger will fire
12). What command explicitly fires a trigger, means can we fire trigger forcefully?
Ans: None, there is no command, you can't fire trigger fourcefuly. A trigger is implicitly fired when the triggering event occurs.
13). How many triggers are possible per table?
Ans: One InsteadOfTrigger and any After Triggers.
14). You have created a DML trigger that fires when an UPDATE operation is performed. You want to ensure that this DML trigger does not fire in the event that no rows are affected by the UPDATE operation. What you will do to implement same?
Ans: I will use @@ROWCOUNT
Ex: IF @@ROWCOUNT = 0 RETURN
15). Is it possible to create trigger on views?
Ans: Yes, you can create only InsteadOf trigger in view.
AFTER trigger allowed only on table.
16). Suppose you need to save/get the count of rows inserted into a table, How you will achieve that?
Ans: I will take the help of trigger and magic table.
SELECT COUNT(*) FROM Inserted
17). You are working with some Transact-SQL code that causes an error. You want to determine the name of the trigger or stored procedure that caused a specific error. You configure the appropriate TRY . . . .CATCH construct, using the code so that you can further diagnose the error. What will you do for that?
Ans: I can use the ERROR_PROCEDURE() system function to determine the name of the trigger or stored procedure in which the error occurred.
18). How can you use @@ROWCOUNT to improve the performance of a trigger?
Ans: You can improve the performance of the trigger by testing whether @@ROWCOUNT is 0 in the very first line of the trigger. When no rows are affected, there is no point in proceeding with the trigger. It must be the first line because @@ROWCOUNT will be set back to 0 by any additional statement.
When the AFTER trigger begins, @@ROWCOUNT will contain the number of rows affected by the outer INSERT, UPDATE, or DELETE statement.
19). You notice a system uses a lot of triggers to enforce foreign key constraints, and the triggers are error-prone and difficult to debug. What changes can you recommend to reduce the use of triggers?
Ans: Foreign key constraints can be implemented by using triggers, but the code can become complex and error prone.
You can recommend instead that the database developers implement true referential integrity by using T-SQL declared foreign key constraints rather thantriggers.
20). Suppose there is an important table that requires some simple logging actions to take place after any changes to the data. The logging is to a custom table built especially to meet application requirements.What recommendation might you make to help implement such a logging action?
Ans: I will recommend that the database developers use a DML AFTER trigger.
This type of trigger executes after an INSERT, UPDATE, or DELETE statement and it can write to the logging table.
21). What are the difference between Trigger and Stored Procedure?
Ans: Differences between Stored Procedures and triggers ---
Kindly share your feedback about our knowledge sharing, you like it, As we categorized all SQL Server questions. It will help us. Thanks happy learning and Best of luck.
TOP 22 SQL SERVER TRIGGER INTERVIEW QUESTIONS ANSWER
1). Explain what is a Triggers in SQL Server?
Ans: Click here for answers
2). What are the type of Triggers?
Ans: There are two types of triggers
- After Triggers (For Triggers)
- Instead Of Triggers
Ans: After Triggers
These triggers run after an insert, update or delete on a table. They are not supported for views.
AFTER TRIGGERS can be classified further into three types as:
- AFTER INSERT Trigger.
- AFTER UPDATE Trigger.
- AFTER DELETE Trigger.
Ans: 32 levels
5). What are magic tables in SQL Server?
Ans: Magic tables are nothing but inserted and deleted which are temporary object created by server internally to hold the recently inserted values in the case of insert and to hold recently deleted values in the case of delete, to hold before updating values or after updating values in the case of update.
6). How many types of Magic tables available in SQL server?
Ans: There are two types of Magic tables available in SQL server:
Inserted
Deleted
7). Suppose you have a view that is formed by joining two tables together, what database object would allow you to insert a new row into both of the two tables joined together?
Ans: An INSTEAD OF trigger
8). If a trigger aborts due to a run-time error and no exception handler exists, what will occur?
Ans: The transaction will be rolled back and control passes to the calling environment.
9). Which T-SQL statement can you include in the code of the trigger to indicate a normal exit from the trigger?
Ans: RETURN
10). If there is an AFTER INSERT trigger on a table, how many times will that trigger fire if you insert 50 rows using a single INSERT?
Ans: Trigger will fire once
11). If a DML trigger fires and executes another DML statement also contains a trigger, what will happen to that second trigger, it will fire or not?
Ans: The second trigger will fire
12). What command explicitly fires a trigger, means can we fire trigger forcefully?
Ans: None, there is no command, you can't fire trigger fourcefuly. A trigger is implicitly fired when the triggering event occurs.
13). How many triggers are possible per table?
Ans: One InsteadOfTrigger and any After Triggers.
14). You have created a DML trigger that fires when an UPDATE operation is performed. You want to ensure that this DML trigger does not fire in the event that no rows are affected by the UPDATE operation. What you will do to implement same?
Ans: I will use @@ROWCOUNT
Ex: IF @@ROWCOUNT = 0 RETURN
15). Is it possible to create trigger on views?
Ans: Yes, you can create only InsteadOf trigger in view.
AFTER trigger allowed only on table.
16). Suppose you need to save/get the count of rows inserted into a table, How you will achieve that?
Ans: I will take the help of trigger and magic table.
SELECT COUNT(*) FROM Inserted
17). You are working with some Transact-SQL code that causes an error. You want to determine the name of the trigger or stored procedure that caused a specific error. You configure the appropriate TRY . . . .CATCH construct, using the code so that you can further diagnose the error. What will you do for that?
Ans: I can use the ERROR_PROCEDURE() system function to determine the name of the trigger or stored procedure in which the error occurred.
18). How can you use @@ROWCOUNT to improve the performance of a trigger?
Ans: You can improve the performance of the trigger by testing whether @@ROWCOUNT is 0 in the very first line of the trigger. When no rows are affected, there is no point in proceeding with the trigger. It must be the first line because @@ROWCOUNT will be set back to 0 by any additional statement.
When the AFTER trigger begins, @@ROWCOUNT will contain the number of rows affected by the outer INSERT, UPDATE, or DELETE statement.
19). You notice a system uses a lot of triggers to enforce foreign key constraints, and the triggers are error-prone and difficult to debug. What changes can you recommend to reduce the use of triggers?
Ans: Foreign key constraints can be implemented by using triggers, but the code can become complex and error prone.
You can recommend instead that the database developers implement true referential integrity by using T-SQL declared foreign key constraints rather thantriggers.
20). Suppose there is an important table that requires some simple logging actions to take place after any changes to the data. The logging is to a custom table built especially to meet application requirements.What recommendation might you make to help implement such a logging action?
Ans: I will recommend that the database developers use a DML AFTER trigger.
This type of trigger executes after an INSERT, UPDATE, or DELETE statement and it can write to the logging table.
21). What are the difference between Trigger and Stored Procedure?
Ans: Differences between Stored Procedures and triggers ---
- When you create a trigger you have to identify event and action of your trigger but when you create s.p you don't identify event and action
- Trigger is run automatically if the event is occured but s.p don't run automatically but you have to run it manually
- Trigger execute implicitly whereas store procedure execute via procedure call from another block.
- Within a trigger you can call specific s.p but within a sp you cannot call atrigger
- We can call a stored procedure from front end (.asp files, .aspx files, .ascx files etc.) but we can't call a trigger from these files.
- Stored procedure can take the input parameters, but we can't pass the parameters as an input to a trigger.
Kindly share your feedback about our knowledge sharing, you like it, As we categorized all SQL Server questions. It will help us. Thanks happy learning and Best of luck.
I hope These interview questions are very useful for freshers and experience.
ReplyDeletethank you sir,
Great
DeletePlease recheck 10th question. We can not insert 50 rows in a single insert. So question seems to be wrong.
DeleteHi we can insert the 50 rows in single insert.
DeleteINSERT INTO table1 (First, Last)
VALUES
('Fred', 'Smith'),
('John', 'Smith'),
('Michael', 'Smith'),
('Robert', 'Smith');
please give the answer for last question
ReplyDeleteAnswer for the last question -
ReplyDeleteDifference between trigger and functions are as follows -
1. Trigger gets executed automatically, whereas functions are called explicitly.
2. Triggers cannot have input parameters whereas functions can have input parameters.
3. Trigger can be used for DML changes to the SQL Server database whereas Functions can have only a select statement and can have only computations.
4. Triggers cannot be a part of any select statement whereas functions can be called anywhere in a select statement.