Explore topic-wise InterviewSolutions in .

YES, only INSTEAD Of TRIGGER.

Yes, only Instead Of trigger.

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

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

It’s same.

It’s same.

USING sp_settriggerorder PROCEDURE.

Using sp_settriggerorder procedure.

ONE INSTEAD Of TRIGGER and any After TRIGGERS.

One Instead Of Trigger and any After Triggers.

YES, only INSTEAD Of TRIGGER.

Yes, only Instead Of trigger.

Reason MULTIPLE SQL Server services are running as WELL as INTELLIGENCE is TURNED on.

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

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.

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.

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.

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.

AFTER triggers are executed after the ACTION of the INSERT, UPDATE, MERGE, or DELETE STATEMENT is PERFORMED successfully.

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

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;

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;

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.

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.

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

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

DML trigger types are:

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

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:

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.

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 you can categorize:

DML trigger types are:

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