SQL Trigger Update

6 Key excerpts on "SQL Trigger Update"

• 

  eBook - PDF

  SQL: 1999

  Understanding Relational Language Components

  • Jim Melton, Alan R. Simon(Authors)
  • 2001(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  If you specify INSERT, then only an INSERTstatement that specifies the subject table can cause the trigger to fire. If you specify UPDATE, then there are several statements that can cause the trigger to fire. These include an UPDATE statement that specifies a search condition (that is, a searched update statement), an UPDATE statement that specifies a cursor name (a positioned update statement), and the corresponding dynamic SQL statements (see Chapter 18, Dynamic SQL). Similarly, if you specify DELETE, 402 Chapter 11 Active Databases and Triggers there are several statements that cause the trigger to fire, including the searched delete statement, the positioned delete statement, and the corresponding dy- namic SQL statements. We think it's worth noting that SQL:1999 does not provide SELECT as a . The capability to fire a trigger when information is retrieved from a table has been discussed a number of times by the designers of SQL, but it is difficult to justify for any purpose other than logging and auditing (for which there are other solutions in many products). We do not expect this capability to be added to SQL in a future revision of the standard, in spite of the fact that a few SQL products do have something corresponding to it. However, political require- ments, such as the European Union's strong privacy laws, might change our expectations in the future. As we'll see in the subsection below called Triggering Conditions, the semantics of triggers are affected by more than merely by identi- fying which type of triggering SQL statement causes the trigger to fire. Statement and Row Triggers Syntax 11-1 includes a piece of syntax called the ; that syntax has several components, one of which determines whether the trigger being defined is a row-level trigger or a statement-level trigger.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Beginning Microsoft SQL Server 2012 Programming

  • Paul Atkinson, Robert Vieira(Authors)
  • 2012(Publication Date)
  • Wrox

    (Publisher)

  so many SQL Server people I meet are) with the distorted notion that triggers are evil and should never be used. Neither will you side with at the other end of the spectrum, who think that triggers are the solution to all the world’s problems. The right answer in this respect is that triggers can do a lot for you, but they can also cause a lot of problems. The trick is to use them when they are the right things to use, and not to use them when they aren’t.

  Some common uses of triggers include:

  • Enforcing referential integrity: Although I recommend using Declarative Referential Integrity (DRI) whenever possible, there are many things that DRI won’t do (for example, referential integrity across databases or even servers, many complex types of relationships, and so on).
  • Creating audit trails: This means writing out records that keep track of not just the most current data, but also the actual change history for each record. This may eventually become less popular with the change-data tracking that SQL Server 2008 added, but triggers are still a pretty popular choice.
  • Creating functionality similar to a CHECK constraint: Unlike CHECK constraints, this can work across tables, databases, or even servers.
  • Substituting your own statements in the place of a user’s action statement: This is typically used to enable inserts in complex views.

  In addition, you have the new, but rarer case of the Data Definition Language (DDL) trigger, which is about monitoring changes in the structure of your table. And these are just a few. So, with no further ado, it’s time to look at exactly what a trigger is.

  WHAT IS A TRIGGER?

  A trigger is a special kind of stored procedure that fires in response to specific events. There are two kinds of triggers: Data Definition Language (DDL) triggers and Data Manipulation Language (DML) triggers.

  DDL triggers fire in response to someone changing the structure of your database in some way (CREATE , ALTER , DROP

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Professional Microsoft SQL Server 2008 Programming

  • Robert Vieira(Author)
  • 2010(Publication Date)
  • Wrox

    (Publisher)

  And these are just a few. So, with no further ado, let's look at exactly what a trigger is. What Is a Trigger?

  A trigger is a special kind of stored procedure that responds to specific events. There are two kinds of triggers: Data Definition Language (DDL) triggers and Data Manipulation Language (DML) triggers.

  DDL triggers fire in response to someone changing the structure of your database in some way (CREATE , ALTER , DROP , and similar statements). These were first added back in SQL Server 2005 and are critical to some installations (particularly high-security installations) but are pretty narrow in use. In general, you will need to look into using these only where you need extreme auditing of changes/history of your database structure. We will save these until last.

  DML triggers are pieces of code that you attach to a particular table or view. Unlike sprocs, where you needed to explicitly invoke the code, the code in triggers is automatically run whenever the event(s) you attached the trigger to occurs in the table. Indeed, you can't explicitly invoke triggers—the only way to do this is by performing the required action in the table that they are assigned to.

  Beyond not being able to explicitly invoke a trigger, you'll find two other things that exist for sprocs but are missing from triggers: parameters and return codes.

  While triggers take no parameters, they do have a mechanism for figuring out what records they are supposed to act on (we'll investigate this further later in the chapter). And, while you can use the RETURN keyword, you cannot return a specific return code (because you didn't explicitly call the trigger, what would you return a return code to?).

  What events can you attach triggers to? The three “action” query types you use in SQL. So, you wind up with triggers based in inserts, updates, and/or deletes (you can mix and match to what events you want the trigger to be attached).

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Microsoft SQL Server 2012 Bible

  • Adam Jorgensen, Jorge Segarra, Patrick LeBlanc, Jose Chinchilla, Aaron Nelson(Authors)
  • 2012(Publication Date)
  • Wiley

    (Publisher)

  Part VII Monitoring and Auditing

  In This Part

  1. Chapter 36 Creating Triggers
  2. Chapter 37 Performance Monitor and PAL
  3. Chapter 38 Using Profiler and SQL Trace
  4. Chapter 39 Wait States
  5. Chapter 40 Extended Events
  6. Chapter 41 Data Change Tracking and Capture
  7. Chapter 42 SQL Audit
  8. Chapter 43 Management Data Warehouse

  Passage contains an image

  Chapter 36 Creating Triggers

  In This Chapter

  1. Creating Instead of and After Triggers
  2. Using the Transaction's Data Within the Trigger
  3. Integrating Multiple Triggers
  4. Creating DDL Database Triggers
  5. Preventing Server or Database Changes
  6. Reading Event Data with XML
  7. Understanding Security Triggers

  SQL Server triggers are special stored procedures attached to table events. They can't be executed directly, but fire only in response to an INSERT , UPDATE , or DELETE event on a table. Users can't bypass a trigger; and unless the trigger sends a message to the client, the end user is unaware of its actions.

  Developing well-behaved triggers involves understanding transaction flow, locking, T-SQL, and stored procedures. Triggers have a few unique elements that require careful planning, but they provide execution of complex business rules and data validation.

  Trigger Basics

  SQL Server triggers fire once per data-modification operation, not once per affected row. This may seem to be a limitation, but developing set-based triggers actually helps ensure clean logic and fast performance.

  Triggers may be created for the three data-modification commands: INSERT , UPDATE , and DELETE .

  Best Practice

  For data integrity, sometimes a trigger is the best solution, but be aware of the potential performance impact. You should consider having business rules enforced by application code instead and only use triggers when this is not feasible.

  SQL Server has two kinds of transaction triggers: instead of triggers and after triggers. They differ in their purpose, timing, and effect, as detailed in Table 36.1

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  How to Cheat at Securing SQL Server 2005

  • Mark Horninger(Author)
  • 2011(Publication Date)
  • Syngress

    (Publisher)

  Chapter 7

  DDL Triggers

  Solutions in this chapter:

  ■  DDL Triggers Explained

  ■  Implementing DDL Triggers

  ■  Managing DDL Triggers

  ■  Scenarios for Deploying DDL Triggers

  ☑  Summary

  ☑  Solutions Fast Track

  ☑  Frequently Asked Questions

  Introduction

  This chapter introduces DDL triggers. It explains what they are, demonstrates how to implement them, and shows you when to implement them. It also provides real-world examples of how and when DDL triggers can be used to help secure an SQL Server.

  DDL Triggers Explained

  Data Definition Language (DDL) is the subset of T-SQL instructions and statements that define structure, whether that structure is objects like tables and views, schemas, or security principals like server logins and database users. Although triggers have been a part of the SQL Server product, they only applied to certain Data Manipulation Language (DML) instructions, including INSERT, UPDATE, and DELETE. Until this latest version of SQL Server, there was no capability to fire a trigger on a DDL statement (such as DROP TABLE).

  Since SQL Server 2000, triggers can fire either before a statement executes, intercepting that statement, or afterward. Triggers that fire before a statement executes are called INSTEAD OF triggers, because they execute “instead of” the statement itself. Triggers that fire after the statement executes (but before the transaction or batch process completes) are called AFTER triggers. AFTER triggers were available prior to SQL Server 2000, and as a result, represent the default trigger behavior. DDL triggers can only be defined as AFTER

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Mastering SQL Server 2008

  • Michael Lee, Gentry Bieker(Authors)
  • 2009(Publication Date)
  • Sybex

    (Publisher)

  Check constraints allow you to use an expression that returns a Boolean result to determine if the row should be allowed in a table. In situations where evaluation of a row takes multiple steps, or requires queries to other tables, triggers can be very useful. Use Foreign Key constraints when possible to enforce referential integrity. A Foreign Key constraint can only create relationships based on equality comparisons, so for more complex relationships, a trigger may be required. When evaluating the use of constraints or triggers, always use constraints if they can meet the functional needs of the application. A trigger should be considered if custom error mes-sages are required in response to error conditions, or in situations where more complex logic is required than constraints can provide. U NDERSTANDING I NSERTED AND D ELETED T ABLES When a DML trigger is executing, it has access to two memory-resident tables that allow access to the data that was modified: Inserted and Deleted. These tables are available only within the body of a trigger for read-only access, and they are automatically created and managed by SQL Server. The structures of the inserted and deleted tables are the same as the structure of the table on which the trigger is defined. 178 | CHAPTER 6 MANAGING DATA INTEGRITY For insert operations, all inserted rows are available in the inserted table. For delete opera-tions, all deleted rows are available in the deleted table. An update can be thought of as a delete followed by an insert. Both the inserted and deleted tables are available within an UPDATE trigger. The deleted table stores the records before modifi-cation (old values), and the inserted table stores records after they were updated (new values). H OW T RIGGERS E XECUTE Triggers execute in response to data modifications. When a modification occurs, the following steps occur: 1. The statement is executed ( INSERT , UPDATE , or DELETE ).

  Sign up to read

  Learn more about book