SQL Triggers

10 Key excerpts on "SQL Triggers"

• 

  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
• 

  No longer available |Learn more

  PostgreSQL 11 Server Side Programming Quick Start Guide

  Effective database programming and interaction

  • Luca Ferrari(Author)
  • 2018(Publication Date)
  • Packt Publishing

    (Publisher)

  Triggers

  Triggers are a powerful way to react to database events. Each time a new tuple is added to a table, a trigger can fire in order to perform some kind of data validation or propagation, or, in general, apply business rules. Triggers are nowadays a fundamental part of any DBMS system, and provide an infrastructure that helps the administrator to enforce data validation and constraints.

  The main idea behind a trigger is that when a specific event happens (such as some data changing), a trigger is fired and a specific piece of executable code runs. PostgreSQL implements triggers by means of functions, so the executable code of a trigger must be implemented as a FUNCTION. As we will see, this function must have a particular prototype and it must be able to access a set of pre-defined variables that keep information about what fired the trigger.

  PostgreSQL provides two main type of triggers, which are fired in reaction to either data manipulation or data definition. This chapter focuses on the powerful trigger infrastructure that PostgreSQL provides.

  This chapter will go through the following concepts:

  • Data manipulation triggers, the most common implementation of triggers, used to validate and propagate data
  • Data definition triggers, a database-wide set of triggers that can intercept DDL statements, allowing you to audit and monitor database DDL activity
  • Implementing trigger behavior in foreign languages, such as Perl and Java

  Passage contains an image

  Data manipulation triggers

  A data manipulation trigger (DML trigger), is a trigger that fires in response to a DML statement (such as INSERT, UPDATE, DELETE, or TRUNCATE) that is executed against a particular table. DML triggers are defined by means of the following:

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  SQL: 1999

  Understanding Relational Language Components

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

    (Publisher)

  The table with which the trigger is defined is called the subject table of the trigger, and the SQL statement that causes a trigger to be fired is called the triggering SQL statement. When a trigger is fired, it causes another SQL statement, called the triggered SQL statement, to be executed. Triggers can be fired even before the actions of the triggering SQL statement. Perhaps even more interesting is the fact that the trigger can have access to the values of the row or rows being inserted, updated, or deleted; and, for rows being updated, the values before the update takes place and the values after the update can both be made available. Triggers, even though they are schema objects themselves, are always asso- ciated with exactly one base table. SQL:1999 does not allow them to be associ- ated with views, although some SQL products provide extensions to allow that capability. What uses might triggers serve? The potential uses are numerous, but we nar- row them down to just a few categories: 9 Logging and auditing: You can define triggers on certain tables---especially tables that have security implications, such as salary information and com- petitive data--to record information about changes made to those tables. That information can be recorded in other tables and might include such information as the authorization identifier under whose control the changes were made, the current time when the changes were made, and even the values being affected in the subject table. This is illustrated in Figure 11-1. 9 Consistency and cleanup: Your application might benefit from allowing relatively simple sequences of SQL statements on certain tables to be sup- ported by triggers whose responsibilities include making corresponding changes to other tables. Thus, an SQL statement that adds a line item to an order might cause a trigger to be fired that updates an inventory table by reserving the quantity of material that was ordered.

  Sign up to read

  Learn more about book
• 

  Available until 21 Nov |Learn more

  Microsoft SQL Server 2012 Administration

  Real-World Skills for MCSA Certification and Beyond (Exams 70-461, 70-462, and 70-463)

  • Tom Carpenter(Author)
  • 2013(Publication Date)
  • Sybex

    (Publisher)

  Using Triggers Triggers can be used as automation assistants. They can be used to prevent accidental data destruction or loss. Triggers can be used by both developers and DBAs for sometimes different purposes and sometimes shared purposes.

  Creating Triggers Triggers are created with the CREATE TRIGGER statement. Triggers can be created at three levels. The first level is the server level, and these are mostly DDL triggers. The second level is the database level, and these, too, are mostly DDL triggers. The final level is the object level (view, table, and so on), and these are mostly DML triggers, although DDL triggers may also be created at this level.

  Understanding Stored Procedures A stored procedure is a collection of T-SQL code stored in the database for use by users and applications. Stored procedures can help abstract security by using the EXECUTE AS clause. They can be used to improve performance because they do not require recompilation by default. They can also be used to centralize business rules for simple creation, enforcement, and maintenance of those rules.

  Creating Stored Procedures Stored procedures are created with the CREATE PROCEDURE statement. Stored procedures can use variables and logical constructions.

  Passage contains an image

  Chapter 13 Implementing Advanced Features

  TOPICS COVERED IN THIS CHAPTER:

  • Understanding and Installing Analysis Services
  • Understanding Integration Services
  • Understanding and Installing Reporting Services
  • Implementing Database Mail
  • Configuring Full-Text Indexing
  • Implementing Transparent Data Encryption
  • Data Compression

  Business intelligence (BI) is a primary function of IT in large enterprises. Even small organizations benefit from BI processes and their output. BI can be defined as the information used to better understand an organization’s position in the marketplace. BI can also be defined as the tools, technologies, and processes used to manage and manipulate an organization’s information so that the market position is better understood. BI is used to understand where an organization is today and how to move in the desired direction. BI is often used as a synonym for decision support; however, decision support

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Wiley Pathways Introduction to Database Management

  • Mark L. Gillenson, Paulraj Ponniah, Alex Kriegel, Boris M. Trukhnov, Allen G. Taylor, Gavin Powell, Frank Miller(Authors)
  • 2012(Publication Date)
  • Wiley

    (Publisher)

  However, most current DBMS products do not support DDL triggers. Triggers are extremely flexible in what they can do. They can include nearly any SQL language executable statement. They can be set up to run after the trig- gering event, the statement that causes the trigger to fire. In the case of SQL Server and some other DBMS products, you can have the trigger run in place of the statement or statements in the triggering event. Triggers can be used to enforce data integrity that cannot be enforced through normal constraints and to set limits on what can be done to server and database objects. You can also use triggers for security auditing, to block and track attempts to perform unau- thorized actions. You will typically limit trigger use to situations where you cannot enforce your requirements with a constraint, such as a check or foreign key constraint. That is because executing a trigger is more resource-intensive than enforcing a constraint and has a bigger potential adverse affect on database performance. 5.2.2 Adjusting Factors Relating to Performance Database performance can be adversely affected by a wide variety of factors. Some factors are a result of application requirements. Normalization can some- times be the source of your performance issues. Often, the most obvious culprit is the need for joining. Joining is an elegant solution to the need for data integration, FOR EXAMPLE Security through Triggers Your database includes several tables that contain sensitive information. A small number of employees are allowed to view the information, but no employees are authorized to directly modify the information. You’ve set access limits, but you need to audit attempts to change the data in the tables. This is a situation where you could use triggers as a security tool. You would create DML triggers on each of the tables that execute when a user attempts to modify or delete table data. You configure the trigger to run instead of the triggering statement.

  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 - ePub

  The Best Damn Exchange, SQL and IIS Book Period

  • Henrik Walther, Mark Horninger, Chris Adams(Authors)
  • 2011(Publication Date)
  • Syngress

    (Publisher)

  Chapter 25

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

  Oracle Performance Tuning for 10gR2

  • Gavin JT Powell(Author)
  • 2011(Publication Date)
  • Digital Press

    (Publisher)

  In fact, database administrators tend to subdivide themselves into one of two camps: Production DBAs and Development DBAs. So what does all this mean? It means triggers are a hopeless substitute for constraints as a method of enforcing referential integrity. Triggers make implementation easier for a programmer but will seriously affect perfor- 2.2 Referential Integrity and Tuning 41 Chapter 2 mance in the long run, perhaps even making your application as much as 10 times slower than it could be. 2.2.2.2.2 Using Triggers for Event Trapping In some relational databases, triggers can be used to trap specific database events, in addition to SQL insert, update, and delete events. In Oracle Database, this is not the case. Triggers are a less efficient substitute for con-straints when implementing referential integrity. Trigger code is slow because coded PL/SQL will execute much slower than Oracle Database internal constraint checks. Trigger code must be parsed and compiled before execution and uses explicit SQL. Explicit SQL competes with all other SQL in the database for use of parsing and execu-tion resources. Note: Compiled PL/SQL coding is now stored in binary objects, implying that PL/SQL is now a compiled, rather than an interpreted, exe-cution. However, because PL/SQL uses the optimizer and the shared pool in the same manner as SQL code, for preparsing and optimization, I have yet to see any improvement in PL/SQL performance. Triggers may require too much coding maintenance. Constraints should be created when the data model is first built, such that a database adminis-trator is in a position during production to be able to switch off specific constraints, in order to help performance. This type of tuning can be diffi-cult with triggers if individual triggers contain multiple aspects of function-ality, such as a trigger detecting all of the insert, update, and deletion events for an entity.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL

  • Joe Celko(Author)
  • 2008(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  Triggers for Constraints

  T HERE IS A myth that triggers have to be used for complex constraints. While there is a place for triggers in a few situations, they are usually avoidable. But more than that, they are procedural code and should be avoided in favor of declarative code that the optimizer can use.

  Furthermore, while there is an ANSI/ISO Standard for triggers, most vendors have highly proprietary implementations, so the code will not easily port. In Standard SQL, a trigger name is unique in the whole schema even though it is attached to a particular base table. It is executed before or after an INSERT , UPDATE , and/or DELETE action. The INSTEAD OF trigger is used on VIEWS that would not otherwise be updatable to change the underlying base tables.

  The model used in Standard SQL is that the action will create a working table named OLD (reserved word) of the rows that qualified for the UPDATE or DELETE action and a table named NEW (reserved word) of the created rows for the INSERT or UPDATE action. The ANSI/ISO Standard is a bit more complex than just this, but this will serve for our discussion.

  17.1 Triggers for Computations

  If you look at posting in newsgroups, you can easily find examples of table declarations with computed columns. The values in these columns are provided by a computation done in a trigger. CREATE TABLE Boxes(box_name CHAR(B) NOT NULL PRIMARY KEY,box_length INTEGER NOT NULL,box_height INTEGER NOT NULL,box_width INTEGER NOT NULL,box_volume INTEGER NOT NULL); This is accompanied by a trigger that has the statement: SET box_volume = box_length * box_height * box_width;

  Depending on your SQL product, you might have to update the table as a whole, or just update the modified rows. The reasoning given for this trigger is to be sure that an UPDATE

  Sign up to read

  Learn more about book