Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - Every time when developer attempts to update the TableA the table is not updated but instead TableB

  • Every time when developer attempts to update the TableA the table is not updated but instead TableB is updated. After a while confused developer figured out the reason for the same – it was trigger created on the table. What are the different types of triggers inside SQL Server 2008 R2 edition?

    Posted on 01-02-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

16  Answers  

Subscribe to Notifications
Previous 1 | 2 Next
  • Score
    5

    TableA has INSTEAD OF trigger and that trigger has code to update TableB that's why when developer attempts to update the TableA the table is not updated but instead TableB is updated.

    SQL Server 2008 R2 edition supports following types of triggers:

    1. DML
    2. DDL
    3. Logon trigger

    DML triggers execute 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 is fired, regardless of whether or not any table rows are affected.

    DDL triggers execute in response to a variety of data definition language (DDL) events. These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations.

    Logon triggers fire in response to the LOGON event that is raised when a user sessions is being established. Triggers can be created 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. Logon triggers do not fire if authentication fails.

    Replied on Jan 2 2012 1:18AM  . 
    Hardik Doshi
    20 · 9% · 2863
  • Score
    5

    Hi All,

    Instead of trigger was created on the first table due to which the first table was not updated. Trigger is a stored piece of Transact SQL code that performs some pre-defined task when an event occurs. There are 3 types of triggers: 1.Data Manipulation Language(DML) Triggers 2.Data Definition Language(DDL) Triggers 3.LOGON Triggers.

    DML Triggers:

    These triggers are fired when modifications are done to the data in a table or view using the following data manipulation commands INSERT,UPDATE and DELETE.The trigger and the statement that fires the trigger are considered as a single transaction which can be rolled back from within the trigger. 2 Types of DML Triggers are: a)AFTER Triggers:are fired only after the completion of the execution of the following INSERT,UPDATE or DELETE statements.These can be specified only on tables.This trigger is default trigger created. b)INSTEAD OF Triggers:are fired instead of the modification being made by the following INSERT,UPDATE or DELETE statements.These can also be specified on views.

    DDL Triggers:

    These triggers are fired only after the completion of execution of the following CREATE,DROP or ALTER statements.Scope of these triggers is the current database or on the current server.These are used to perform administrative tasks.

    LOGON Triggers:

    These triggers are fired when an user session is established with an instance of SQL Server.This is fired only when the authentication is successful.

    Replied on Jan 2 2012 1:47AM  . 
    SreelekhaVikram
    1345 · 0% · 17
  • Score
    6

    Every time when developer attempts to update the TableA the table is not updated but instead TableB is updated.

    This is due to INSTEAD OF trigger on TableA to update TableB.

    SQL Server 2008R2 Supports the following trigger:

    1. DML Triggers DML triggers are invoked when a data manipulation language (DML) event takes place in the database. DML events include INSERT, UPDATE, or DELETE statements that modify data in a specified table or view.

    Eg: AFTER Triggers,
    INSTEAD OF Triggers,
    CLR Triggers
    

    2. DDL Triggers DDL triggers, like regular triggers, fire stored procedures in response to an event. However, unlike DML triggers, they do not fire in response to UPDATE, INSERT, or DELETE statements on a table or view. Instead, they fire in response to a variety of Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, and DROP. Certain system stored procedures that perform DDL-like operations can also fire DDL triggers.

    3. Logon Triggers Logon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established. You can use logon triggers to audit and control server sessions, such as by tracking login activity, restricting logins to SQL Server, or limiting the number of sessions for a specific login.

    Replied on Jan 2 2012 2:08AM  . 
    Latheesh NK
    51 · 4% · 1178
  • Score
    5

    The mentioned problem was because of the "Instead of Trigger" in place on TableA. Instead of updating the TableA, the instead of trigger is executed and updates the TableB.

    There are DDL, DML, Logon and Instead of triggers.

    DML Triggers: DML triggers are after triggers which are executed only after triggering SQL statement (insert, update or delete) has executed successfully. If the constraints are in place on a trigger table, they are executed after "instead of trigger" but before "after trigger".

    DDL Triggers: DDL triggers fire on DDL statements such as Create, Alter, Drop, Update Statistics and DCL statements such as Grant, Deny, Revoke. I must mention that Truncate does not activate any trigger because the operation does not log any individual row deletions.

    Logon Triggers: Logon trigger fires on logon event. LOGON event is raised when a user session is established with an instance of SQL Server.

    Instead of Trigger: This is a special type of trigger that executes instead of the triggering SQL statement. The instead of triggers are not created for DDL statements and logon event.

    Replied on Jan 2 2012 4:35AM  . 
    manik
    752 · 0% · 42
  • Score
    6

    Triggers are a special form of stored procedure that are executed automatically when data manipulation language (DML) or data definition language (DDL) commands are executed.

    The generic syntax for creating a trigger is

    CREATE TRIGGER [ schemaname . ]triggername
    ON { table | view }
    [ WITH <dml_trigger_option> [ ,...n ] ]
    { FOR | AFTER | INSTEAD OF }
    { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
    [ WITH APPEND ]
    [ NOT FOR REPLICATION ]
    AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }
    

    SQL Server triggers fire once per data-modification operation, not once per affected row. This is different from Oracle, which can fire a trigger once per operation, or once per row. Triggers can be used either for DDL or for DML statements.

    DML triggers are created against a table or a view and are defined for a specific event: INSERT, UPDATE, or DELETE. When you execute the event for which a trigger is defined, SQL Server automatically executes the code within the trigger, which also is known as “firing” the trigger. SQL Server has two kinds of transaction triggers: instead of triggers and after triggers. They differ in their purpose, timing, and effect, as mentioned below:

    Instead of Trigger are

    • Simulated but not Executed
    • Appears before PK and FK constraints
    • Only a single implementation possible for a table
    • Can be applied on views
    • Can be nested, depending on server options,
    • Cannot be recursive

      After Trigger are:

    • Executed, but can be rolled back in the trigger

    • Comes after the transaction is complete, but before it is committed
    • Multiple triggers possible per table event
    • Cannot be applied on views
    • Can be nested, depending on server options,
    • Recursion depends on server options

    DDL triggers are a special kind of trigger that fire in response to Data Definition Language (DDL) statements. They can be used to perform administrative tasks in the database such as auditing and regulating database operations.

    Logon Triggers Logon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established. You can use logon triggers to audit and control server sessions, such as by tracking login activity, restricting logins to SQL Server, or limiting the number of sessions for a specific login.

    Replied on Jan 2 2012 5:36AM  . 
    Vishal Soni
    1028 · 0% · 25
  • Score
    2
    Replied on Jan 2 2012 8:44AM  . 
    Yogesh Kamble
    142 · 1% · 349
  • Score
    2

    SQL Server 2008 R2 support DML(After,Instead of,CLR),DDL & Logon trigger.

    Replied on Jan 2 2012 7:58PM  . 
    Anup Warrier
    209 · 1% · 224
  • Score
    7

    A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. A trigger cannot be called or executed. Triggers can execute stored procedures.

    SQL 2008 R2 supports following types of triggers:

    1. DML Triggers
    2. DDL Triggers
    3. Logon Triggers

    1. DML Triggers

    DML triggers execute when a user tries to modify data through a data manipulation language (DML) event like INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected. DML triggers are frequently used for enforcing business rules and data integrity.

    SQL Server provides 2 types of DML triggers, AFTER and INSTEAD OF (added in SQL Server 2000).

    AFTER Triggers:- AFTER triggers take place after the action has taken place and are the default trigger created if we do not specify the trigger type in the CREATE TRIGGER statement. When an AFTER trigger is applied to a table the action takes place, then the trigger fires. Because the trigger is part of the transaction any error within the trigger will cause the entire transaction to fail and rollback. A typical use for an AFTER trigger is to log the action to an audit or logging table.

    INSTEAD OF Triggers:- INSTEAD OF triggers take place instead of the modification being made. They are Microsoft's answer to BEFORE triggers available in other RDBMS systems. If I define an INSTEAD OF trigger on a table FOR INSERT the trigger will fire BEFORE the data is inserted into the table. If I do not repeat the INSERT within the trigger then the insert will not take place.

    The following T-SQL statements are not allowed in a DML Trigger ALTER DATABASE,CREATE DATABASE,DROP DATABASE,LOAD DATABASE LOAD LOG,RECONFIGURE,RESTORE DATABASE,RESTORE LOG

    2. DDL Triggers(from SQL 2005) DDL triggers execute in response to data definition language (DDL) events. These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations. They can be used to perform administrative tasks in the database such as auditing and regulating database operations. Use DDL triggers when you want to do the following:

    • You want to prevent certain changes to your database schema.
    • You want something to occur in the database in response to a change in your database schema.
    • You want to record changes or events in the database schema.

    DDL triggers cannot be used as INSTEAD OF triggers. DDL triggers can fire in response to a Transact-SQL event that is processed in the current database or on the current server.

    3. Logon Trigger(From SQL server 2005)

    Logon triggers fire in response to the LOGON event that is raised when a user sessions is being established with an instance of SQL Server. Triggers can be created 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. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established. Therefore, 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.

    Logon triggers do not fire if authentication fails. Distributed transactions are not supported in a logon trigger. Error 3969 is returned when a logon trigger containing a distributed transaction is fired.

    Reference :

    http://msdn.microsoft.com/en-us/library/ms189799.aspx

    http://www.sqlservercentral.com/articles/Triggers/64214/

    Replied on Jan 2 2012 11:18PM  . 
    Sineetha
    106 · 2% · 492
  • Score
    4

    Instead of updating the TableA, the 'Instead of trigger' is executed and updates the TableB. There are DDL, DML, Logon and Instead of triggers.

    DML Triggers are after triggers which are executed only after triggering SQL statement (insert, update or delete) has executed successfully. If the constraints are in place on a trigger table, they are executed after 'instead of trigger' but before 'after trigger'. DDL Triggers fire on DDL statements such as Create, Alter, Drop, Update Statistics and DCL statements such as Grant, Deny, Revoke. Logon Triggers fires on logon event, which is raised when a user session is established with an instance of SQL Server. Instead of Trigger executes instead of the triggering SQL statement and are not created for DDL statements and logon event.

    Replied on Jan 3 2012 3:53AM  . 
    oxia_1
    2376 · 0% · 5
  • Score
    2

    INSTEAD OF trigger is written on TableA that updates the TableB.

    INSTEAD OF triggers are executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables, where they can extend the types of updates a view can support.

    The 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.

    DML Trigger Planning Guidelines

    Specifying When a DML Trigger Fires

    Replied on Jan 4 2012 2:49AM  . 
    softsara
    1916 · 0% · 10
Previous 1 | 2 Next

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.