SQL Interview Questions and Answers - Part 15
Are you preparing for the SQL Interview? If yes, then you are at the right place. This is the SQL Interview Questions and Answers article series. Here we will see the Top 100+ SQL Interview Questions with Answers.
Please visit our YouTube Channel for Interviews and other videos by below link:
Please read the complete Design Pattern, C#, MVC, WebAPI, and .Net Framework Interview Questions and Answers article series here.
Introduction
This is the 15th part of the SQL Interview Questions and Answers article series. Each part contains eight to ten SQL Interview Questions with Answers. Please read all the SQL Interview Questions list here.
I will highly recommend to please read the previous parts over here before continuing the current part:
SQL Interview Questions and Answers - Part 15
Q131. What is a Trigger in SQL?
Triggers are nothing but T-SQL logic like stored procedures that can be executed automatically before or after any DML (Insert, Update, Delete) or DDL(CREATE, ALTER, DROP) operation happens on a table.
A trigger is a special type of stored procedure or set of T-SQL statements that automatically runs when an event occurs in the database server. The trigger name must follow the rules for identifiers, except that the trigger name can't start with # or ##.
You can design a Transact-SQL trigger to do certain actions based on an UPDATE or INSERT modifications to specific columns. A trigger requires to specify at least one option from INSERT, UPDATE or DELETE. You can use any combination of these options in any order in the trigger definition.
A trigger is designed to check or change data based on a data modification or definition statement; it shouldn't return data to the user.
You can also use the NOT FOR REPLICATION option in the definition to indicate that the trigger shouldn't be run when a replication agent modifies the table that's involved in the trigger.
Triggers also work in transactions and while they're open, they lock resources. The lock remains in place until the transaction is confirmed by COMMIT or ROLLBACK.
The longer a trigger runs, the higher the probability that another process is then blocked.
To release the trigger for a command that doesn't change any rows, employ the system variable ROWCOUNT_BIG.
IF (ROWCOUNT_BIG() = 0)
RETURN;
The trigger also follows the Deferred Name Resolution. SQL Server allows for stored procedures, triggers, and batches to refer to tables that don't exist at compile time. This ability is called deferred name resolution.
After the CREATE TRIGGER has finished running, event_group also acts as a macro by adding the event types it covers to the sys.trigger_events catalog view. You can use sys.triggers and sys.trigger_events catalog view to see the events that cause a trigger to fire.
SELECT TE.*
FROM sys.trigger_events AS TE
JOIN sys.triggers AS T ON T.object_id = TE.object_id
WHERE T.parent_class = 0 AND T.name = 'trigger_name';
Q132. What are the Advantages and Disadvantages of Triggers in SQL?
Trigger Fundamental Points:
- The CREATE TRIGGER statement must be the first statement in the batch and can apply to only one table.
- A trigger can be created only in the current database. However, a trigger can reference objects outside the current database as well.
- The same trigger action can be defined for more than one user action (INSERT and UPDATE) in the same CREATE TRIGGER statement.
- The INSTEAD OF DELETE/UPDATE triggers can't be defined on a table that has a Foreign Key with a cascade on DELETE/UPDATE action defined.
- Any SET statement can be specified inside a trigger. The SET option selected remains in effect during the execution of the trigger and then reverts to its former setting.
- When a trigger fires, results are returned back to the calling application, just like with stored procedures. To prevent results from being returned to an application because of a trigger firing, don't include either SELECT statements that return results or statements that carry out a variable assignment in a trigger.
- If variable assignment must occur in a trigger, use a SET NOCOUNT statement at the start of the trigger to prevent the return of any result sets.
Limitations of Trigger:
- The TRUNCATE command cannot activate the Triggers because the operation doesn't log individual row deletions.
- The WRITETEXT statement, whether logged or unlogged, doesn't activate a trigger.
- You can not use CREATE, ALTER, DROP or RESTORE any database and RESTORE LOG or RECONFIGURE command in the body definition of the DML trigger.
- Malicious code inside triggers can run under escalated privileges. By default, both DML and DDL triggers execute under the context of the user that calls the trigger.
- The caller of a trigger is the user that executes the statement that causes the trigger to run. This default behavior can be exploited by users who want to introduce malicious code in the database or server instance. Both DML and DDL triggers are open to this kind of security threat.
Q133. What are the different types of Triggers available in SQL?
There are mainly three categories of triggers in SQL:
1. DML Trigger:
DML triggers run when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event fires, whether table rows are affected or not.
Again DML Triggers can be categorized into two sub-types as well:
i) AFTER or FOR Trigger:
AFTER triggers are executed after the action of the INSERT, UPDATE, MERGE or DELETE statement is performed.
AFTER triggers are never executed if a constraint violation occurs; therefore, these triggers cannot be used for any processing that might prevent constraint violations.
For every INSERT, UPDATE, or DELETE action specified in a MERGE statement, the corresponding trigger is fired for each DML operation.
ii) INSTEAD OF Trigger:
INSTEAD OF triggers override the standard actions of the triggering statement. Therefore, they can be used to perform error or value checking on one or more columns and perform additional actions before inserting, updating, or deleting the row or rows.
The primary advantage of INSTEAD OF triggers is that they enable views that would not be updatable to support updates.
2. DDL Triggers:
DDL triggers run in response to a variety of data definition language (DDL) events. They can also launch stored procedures in response to an event. The statement types include CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS. Certain system stored procedures that carry out DDL-like operations can also fire DDL triggers.
But, unlike standard triggers, they don't run in response to UPDATE, INSERT, or DELETE statements on a table or view. Instead, they primarily run in response to data definition language (DDL) statements.
The DDL Triggers again can be divided into two sub-categories:
i) T-SQL DDL Trigger:
It is 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 DROP, ALTER, etc is executed by any user.
ii) CLR DDL Trigger:
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.
DDL triggers fire only after the DDL statements that trigger them are run. DDL triggers cannot be used as INSTEAD OF triggers. DDL triggers do not fire in response to events that affect local or global temporary tables and stored procedures.
3. CLR Trigger:
This is a Common Language Runtime trigger. A CLR Trigger can be either an AFTER or INSTEAD OF trigger. A CLR trigger can also be a DDL trigger as well.
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.
4. Logon Trigger:
Logon triggers carry out stored procedures in response to a LOGON event. This event happens when a user session is established with an instance of SQL Server.
Q134. What is DML Trigger in SQL? What is the application of DML Triggers?
The DML triggers are special procedures or sets of SQL statements that run when a user tries to modify data through a DML (Data Manipulation Language) event.
DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event fires, whether table rows are affected or not. The Insert, Update and Delete statements are also known as Triggering SQL statements as these statements are responsible for the trigger to fire.
Multiple DML triggers of the same type (INSERT, UPDATE, or DELETE) on a table allow multiple, different actions to take place in response to the same modification statement.
To create a DML trigger, a user requires ALTER permission on the table or view on which the trigger is being created.
DML Triggers Fundamental Points:
You can't define DML triggers on Local or Global Temporary Tables. DML triggers use the deleted and inserted logical (conceptual) tables.
DML triggers are scoped to the schema of the table or view on which they're created. So schema_name can't be specified for DDL or logon triggers.
If constraints exist on the trigger table, they're checked after the INSTEAD OF trigger runs and before the AFTER trigger runs. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger isn't fired.
You can not use CREATE DATABASE, ALTER DATABASE, DROP DATABASE or RESTORE DATABASE and RESTORE LOG or RECONFIGURE command in the body definition of the DML trigger.
Also, you cannot use CREATE INDEX, ALTER INDEX or DROP INDEX, DROP TABLE, DBCC DBREINDEX, ALTER TABLE, or ALTER PARTITION FUNCTION commands in DML Trigger.
Applications of DML Triggers:
DML triggers are frequently used for enforcing business rules and data integrity. They can guard against malicious or incorrect INSERT, UPDATE, and DELETE operations and enforce other restrictions that are more complex than those defined with CHECK constraints.
DML triggers are similar to constraints in that they can enforce entity integrity or domain integrity. DML triggers are most useful when the features supported by constraints cannot meet the functional needs of the application.
DML triggers can cascade changes through related tables in the database; however, these changes can be executed more efficiently using cascading referential integrity constraints as well.
There is a limitation on how SQL Server handles referential integrity which makes it unavailable on objects that are on different databases. In order to overcome this limitation, we can use DML Triggers to implement foreign keys on objects amongst databases.
Basically, the trigger will perform some validation before changing or deleting any data on the referenced table allowing us to code our own algorithm to check for referential integrity violations.
DML triggers can disallow or roll back changes that violate referential integrity, thereby canceling the attempted data modification.
Unlike CHECK constraints, DML triggers can reference columns in other tables. They can evaluate the state of a table before and after a data modification and take actions based on that difference.
Constraints can communicate about errors only through standardized system error messages. If your application requires or can benefit from, customized messages and more complex error handling, you must use a trigger.
Q135. What is AFTER or FOR TRIGGER in SQL? What is the use of AFTER DML Trigger?
The AFTER triggers are executed only after the DML action like the INSERT, UPDATE, MERGE, or DELETE statement is performed. For every INSERT, UPDATE, or DELETE action specified in a MERGE statement, the corresponding trigger is fired for each DML operation.
The FOR or AFTER keyword in the definition of trigger specifies that the DML trigger fires only when all operations specified in the triggering SQL statement have launched successfully.
This means an AFTER trigger is run only after the triggering SQL statement has run successfully. This successful execution includes all referential cascade actions and constraint checks associated with the object updated or deleted.
You can specify the first and last AFTER triggers to be run on a table by using sp_settriggerorder. You can specify only one first and one last AFTER trigger for each INSERT, UPDATE, and DELETE operation on a table.
If there are other AFTER triggers on the same table, they're randomly run. If an ALTER TRIGGER statement changes a first or last trigger, the first or last attribute set on the modified trigger is dropped, and you must reset the order value by using sp_settriggerorder.
The below trigger will prevent any record update in the employee table.
CREATE TRIGGER tgrPreventUpdation
ON Employee
FOR UPDATE
AS
BEGIN
PRINT 'Record Updation is Prevented by Admin...'
ROLLBACK TRANSACTION
END
The below trigger will prevent the record deletion from the employee table.
CREATE TRIGGER tgrPreventDeletion
ON Employee
AFTER DELETE
AS
BEGIN
PRINT 'Record Deletion is Prevented by Admin...'
ROLLBACK TRANSACTION
END
AFTER TRIGGER Fundamental Points
- All Referential Cascade actions and Constraint Checks must also succeed before this DML AFTER trigger fires.
- You can not define AFTER triggers on views. They can only be defined on permanent tables.
- The Trigger will be invoked even before checking the existence of the actual record in case of an UPDATE and DELETE trigger.
- You can define multiple AFTER Triggers per triggering action like UPDATE, INSERT, and DELETE.
- The AFTER DML Trigger executes after the INSERTED and DELETED magic tables are created.
- The AFTER triggers are never executed if a constraint violation occurs; therefore, these triggers cannot be used for any processing that might prevent constraint violations.
- An AFTER Trigger does not recursively fire an INSTEAD OF trigger on the same table.
Q136. What is INSTEAD OF TRIGGER in SQL? How can you use the INSTEAD OF Trigger to update the VIEW?
The INSTEAD OF triggers are the DML triggers that are fired instead of the triggering event such as the INSERT, UPDATE or DELETE on a table. The INSTEAD OF trigger allows you to skip or cancel an INSERT, DELETE, or UPDATE statement to a table or a view and execute other statements defined in the trigger instead.
An INSTEAD OF triggers overrides the standard DML actions of the triggering statement. So the actual INSERT, DELETE, or UPDATE triggering operation does not occur at all.
If an INSTEAD OF trigger defined on a table runs a statement against the table that would ordinarily fire the INSTEAD OF trigger again, the trigger isn't called recursively.
Instead, the statement processes as if the table had no INSTEAD OF trigger and starts the chain of constraint operations and AFTER trigger executions.
When an INSTEAD OF trigger defined on a view runs a statement against the view that would ordinarily fire the INSTEAD OF trigger again, it's not called recursively. Instead, the statement is resolved as modifications against the base tables underlying the view.
INSTEAD OF Trigger Fundamental Points:
- You can reference both tables and views for the INSTEAD OF Trigger.
- However, you can not define INSTEAD OF triggers on updatable views that use WITH CHECK OPTION.
- The INSTEAD OF UPDATE and DELETE triggers are not allowed on tables that are targets of cascaded referential integrity constraints.
- The INSTEAD OF trigger executes before any Constraint processing on the place of the triggering action.
- You can't specify INSTEAD OF trigger for DDL or LOGON triggers.
- SQL Server allows the update of text, ntext, or image columns through the INSTEAD OF trigger on tables or views.
- At most, you can define one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement on a table or view.
- You can also define views on views where each view has its own INSTEAD OF trigger.
- For INSTEAD OF triggers, you can't use the DELETE option on tables that have a referential relationship, specifying a cascade action ON DELETE.
- Similarly, the UPDATE option isn't allowed on tables that have a referential relationship, specifying a cascade action ON UPDATE.
Applications of INSTEAD OF Trigger:
The primary use of INSTEAD OF triggers is that they enable views that would not be updatable to support updates.
Another advantage of INSTEAD OF triggers is that they enable you to code logic that can reject parts of a batch while letting other parts of a batch to succeed.
The below INSTEAD OF trigger will update the record from view which we have created in the previous part.
CREATE TRIGGER tgr_vwGetEmplyeeList_InsteadOfUpdate
ON vwGetEmplyeeList
INSTEAD OF UPDATE
AS
BEGIN
IF(UPDATE(ID))
BEGIN
RAISERROR('ID cannot be updated', 16, 1)
RETURN
END
UPDATE Employee
SET Name = I.Name,
RoleName = I.RoleName,
Salary = I.Salary
FROM INSERTED I
INNER JOIN Employee E ON E.ID = I.ID
END
Q137. What is DDL TRIGGER in SQL? What is the application of DDL Triggers?
DDL triggers are introduced with SQL Server 2005. It is used to restrict certain DDL operations such as CREATE, ALTER and DROP commands.
But, unlike standard triggers, they don't run in response to UPDATE, INSERT, or DELETE statements on a table or view. Instead, they primarily run in response to data definition language (DDL) statements.
DDL triggers run in response to a variety of data definition language (DDL) events. They can also launch stored procedures in response to an event.
The SQL statement types include CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS. Certain system stored procedures that carry out DDL-like operations can also fire DDL triggers like sp_rename.
The DDL and logon trigger capture information about the triggering event by using the EVENTDATA function. So the information about an event that fires a DDL trigger, and the subsequent changes caused by the trigger, can be retrieved by using the EVENTDATA function.
Creating a DDL trigger with server scope or a logon trigger requires CONTROL SERVER permission on the server. But for creating a DDL trigger with database scope requires ALTER ANY DATABASE DDL TRIGGER permission in the current database.
The below DDL trigger will prevent from dropping any table from the database:
CREATE TRIGGER tgrPreventDropTable
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
PRINT 'YOU CAN NOT DROP TABLES'
ROLLBACK TRANSACTION
END
Multiple triggers need to be created for each DDL event. To be able to create, alter or drop a table we either have to disable or delete the trigger. You can use Disable Trigger command to do that:
DISABLE TRIGGER tgrPreventDropTable ON DATABASE
Similarly, you can enable as well:
ENABLE TRIGGER tgrPreventDropTable ON DATABASE
You can't use functions such as OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY, and OBJECTPROPERTYEX for querying metadata about DDL triggers. You need to use the catalog views instead.
Server-scoped DDL triggers appear in the SQL Server Management Studio Object Explorer in the Triggers folder.
DDL Trigger Fundamental Points:
DDL trigger fires only after the events that fired them are executed successfully. So they cannot be used as INSTEAD OF triggers.
DDL triggers don't fire in response to events that affect local or global temporary tables and stored procedures.
DDL triggers do not create the special INSERTED and DELETED tables as well.
We cannot implement business logic in DDL Trigger. For that, we always need to use DML Triggers.
Types of DDL Triggers:
Unlike DML triggers, DDL triggers aren't scoped to schemas. They can be database scoped or server scoped. So there are two types of DDLs triggers available in SQL Server.
- Database Scoped DDL Trigger
- Server Scoped DDL Trigger
A Database Scoped DDL Trigger will prevent the users from creating, altering, or dropping tables only from the database on which it is created.
But, if we have another database on the same server, then the users will be able to create, alter or drop tables in that database. So, if we want to prevent the users from creating, altering, or dropping tables from that database then we need to create the trigger again in that particular database.
Creating a server-scoped DDL trigger in SQL Server is very much similar to creating a database-scoped DDL trigger, except that we will have to change the scope to ALL Serve.
The DDL triggers can be created in a specific database or at the server level. If we set the scope to server-level then it is applied to all the databases of that server.
CREATE TRIGGER tgrServerScopedDDLTrigger
ON ALL SERVER
FOR DROP_TABLE
AS
BEGIN
PRINT 'You cannot drop a table in any database of this server'
ROLLBACK TRANSACTION
END
You can disable/enable the server scoped trigger as well:
DISABLE TRIGGER tgrServerScopedDDLTrigger ON ALL SERVER
Application of DDL Trigger:
- To Prevent certain changes to the schema
- For tracking and logging the changes made to the database schema
- For a quick response to any change in the database schema.
Q138. What is LOGON TRIGGER in SQL? What is the application of LOGON Triggers?
Logon triggers carry out stored procedures in response to a LOGON event. This event happens when a user session is established with an instance of SQL Server. Logon triggers don't fire if authentication fails.
Logon triggers fire in response to the LOGON event that's raised when a user's session is being established. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is established.
So, all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log.
You can create triggers directly from Transact-SQL statements or from methods of assemblies that are created in the Microsoft .NET Framework common language runtime (CLR) and uploaded to an instance of SQL Server.
The Logon Triggers are DDL Triggers and they are created at the Server Level. They are introduced in SQL Server 2005 SP2.
Distributed transactions aren't supported in a logon trigger. Error 3969 returns when a logon trigger that contains a distributed transaction fires.
Application of Logon Triggers:
- For tracking the Login Activity
- For limiting the number of concurrent sessions for a single user
- For restricting logins to SQL Server based on time of day, hostnames, application names
The following logon trigger will limit the maximum number of open connections for a user to 2 except the sa user.
CREATE TRIGGER tgr_ConnectionLimitLogonTrigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
DECLARE @LoginName NVARCHAR(100)
SET @LoginName = ORIGINAL_LOGIN()
IF @LoginName <> 'sa'
AND
( SELECT COUNT(*)
FROM sys.dm_exec_sessions
WHERE Is_User_Process = 1 AND
Original_Login_Name = @LoginName
) > 2
BEGIN
PRINT 'Third session for the user ' + @LoginName + ' is blocked'
ROLLBACK
END
END
Q139. What is the difference between Recursive Trigger and Nested Trigger in SQL?
Recursive Triggers:
SQL Server also supports recursive invocation of triggers when the RECURSIVE_TRIGGERS setting is enabled using ALTER DATABASE. Recursive triggers enable the following types of recursion to occur:
Indirect recursion: With indirect recursion, an application updates table T1. This fires trigger TR1, updating table T2. Trigger T2 then fires and updates table T1.
Direct recursion: In the direct recursion, the application updates table T1. This fires trigger TR1, updating table T1. Because table T1 was updated, trigger TR1 fires again, and so on.
Disabling the RECURSIVE_TRIGGERS setting only prevents direct recursions. To disable indirect recursion also, set the nested triggers server option to 0 by using sp_configure.
If any one of the triggers carries out a ROLLBACK TRANSACTION, regardless of the nesting level, no more triggers are run.
Nested Triggers:
You can nest triggers to a maximum of 32 levels. If a trigger changes a table on which there's another trigger, the second trigger activates and can then call the third trigger, and so on.
If any trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger is canceled.
To disable nested triggers, set the nested triggers option of sp_configure to 0 (off). The default configuration supports nested triggers.
If nested triggers are off, recursive triggers are also disabled, despite the RECURSIVE_TRIGGERS setting that's set by using ALTER DATABASE.
The first AFTER trigger nested inside an INSTEAD OF trigger fires even if the nested triggers server configuration option is 0. But, under this setting, the later AFTER triggers don't fire.
Q140. What are the MAGIC TABLES in SQL? What is the use of INSERTED and DELETED tables in SQL?
SQL DML trigger statements use two special tables i.e the DELETED and INSERTED tables. SQL Server automatically creates and manages these two magic tables.
You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions. However, you cannot directly modify the data in the tables or perform data definition language (DDL) operations on the tables, such as CREATE INDEX.
They're structurally similar to the table on which the trigger is defined, that is, the table on which the user action is tried. The deleted and inserted tables hold the old values or new values of the rows that may be changed by the user action.
DELETED Magic Table:
The deleted table stores copies of the affected rows in the trigger table before they were changed by a DELETE or UPDATE statement.
During the execution of a DELETE or UPDATE statement, the affected rows are first copied from the trigger table and transferred to the deleted table.
SELECT * FROM DELETED;
INSERTED Magic Table:
The inserted table stores copies of the new or changed rows after an INSERT or UPDATE statement.
During the execution of an INSERT or UPDATE statement, the new or changed rows in the trigger table are copied to the inserted table.
The rows in the inserted table are copies of the new or updated rows in the trigger table.
SELECT * FROM INSERTED;
An update transaction is similar to a delete operation followed by an insert operation. So during the execution of an UPDATE statement, the following sequence of events occurs:
- The original row is copied from the trigger table to the deleted table.
- The trigger table is updated with the new values from the UPDATE statement.
- The updated row in the trigger table is copied to the inserted table.
This allows you to compare the contents of the row before the update (in the deleted table) with the new row values after the update (in the inserted table).
Limitations of Magic Tables:
SQL Server does not allow for text, ntext, or image column references in the inserted and deleted tables for AFTER triggers. However, these data types are included for backward compatibility purposes only.
Both AFTER and INSTEAD OF triggers support varchar(max), nvarchar(max), and varbinary(max) data in the inserted and deleted tables.
Because the inserted and deleted tables are virtual, memory-resident tables, properties such as statistics or indexes are not available.
Though some cardinality information is exposed from these tables, you should exercise care when considering the number of rows to be temporarily stored there.
To Be Continued Part-16...
Recommended Articles
Thanks for visiting this page. Please follow and join us on LinkedIn, Facebook, Telegram, Quora, YouTube, Twitter, Pinterest, Tumbler, and VK for regular updates.
Posts
No comments:
Post a Comment
Please do not enter any HTML. JavaScript or spam link in the comment box.