Creating Triggers in SQL

11 Key excerpts on "Creating Triggers in SQL"

• 

  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)

  By the time we're done, you should have an idea of just how complex is the decision about when and where not to use triggers. You'll also have an inkling of just how powerful and flexible they can be.

  Most of all, if I've done my job well, you won't be a trigger extremist (which 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 the other end of the spectrum: those 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:

  • Enforcement of 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). The use of triggers for RI is becoming very special case, but it's still out there.
  • Creating audit trails, which means writing out records that keep track of not just the most current data but also the actual change history for each record.
  • Functionality similar to a CHECK constraint, but which works across tables, databases, or even servers.
  • Substituting your own statements in the place of a user's action statement (usually used to enable inserts in complex views).

  In addition, you have the new but likely much more rare case (as I said, they are new, so only time will tell for sure) DDL trigger—which is about monitoring changes in the structure of your table. 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.

  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

  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
• 

  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

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

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

  SQL Interview Questions

  A complete question bank to crack your ANN SQL interview with real-time examples

  • Prasad Kulkarni(Author)
  • 2019(Publication Date)
  • BPB Publications

    (Publisher)

  RE-CREATE the trigger.

  DROP action can be completed by using SQL Management Studio or Query Analyzer.

  If you want to use the SQL Management Studio then here are the steps:

  1. Connect to database engine and open database.
  2. Expand the database tables.
  3. Select the table on which we want to create triggers.
  4. Right click on Triggers and select Delete . Refer to the following screenshot:

  Figure 8.28

  Using Query Analyzer, you need to fire DROP query as the following text:

  DROP TRIGGER trigger1

  If you fire the above query, trigger1 will delete from database. So to create NEW trigger you need to use CREATE statement. If you don’t want to use DROP and CREATE trigger again, then you can use MODIFY option from SQL Management Studio, check out the following screenshot:

  Figure 8.29

  DDL triggers

  As the name suggests, these triggers are fired when any DDL event occurs. This event may CREATE , DROP , ALTER , and TRUNCATE statement. If you want to prevent the database architecture changes by someone else, then you can take use of DDL triggers. DDL triggers are of the following types:

  1. TRANSACT-SQL DDL trigger: This is a special type of trigger that executes a series of SQL commands/statements if any DDL event occurs, like CREATE , ALTER TABLE , DATABASE , DROP , or TRUNCATE event. Check out the following sample: CREATE TRIGGER testTrigger ON testDB FOR ALTER_TABLE AS BEGIN INSERT INTO EmpMaster(EMPID, Name,Address, DeptID, City_Col) END
     The above query represents DDL trigger where the trigger is fired on database testDB if any ALTER TABLE action is fired on it and as a result it will fire INSERT query on EmpMaster table.
  2. CLR DDLtriggers: As we know, these triggers are executed by CLR carried out by programming language. CLR DML and DDL triggers are the same, except that DDL triggers are fired only after DDL statements are executed, similarly DML triggers are fired only after DML statements are executed. The creation, modification and deletion of the DDL triggers are the same like DML triggers; we can use either SQL Management Studio or can use Query analyzer to operate them.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Microsoft SQL Server 2005 Programming For Dummies

  • Andrew Watt(Author)
  • 2007(Publication Date)
  • For Dummies

    (Publisher)

  This creates issues when you want to test the code in the trigger because only the relevant event, or events, causes the code in the trigger to execute. 224 Part III: Creating Databases and Database Objects with Transact-SQL One way to approach debugging is to create a stored procedure that contains a statement, or statements, that you know will fire the trigger. For example, the following stored procedure fires the AFTER trigger on the AuditedTable table that you create earlier in this chapter. CREATE PROCEDURE TestInsertTrigger AS INSERT INTO AuditedTable VALUES (‘This is from the test stored procedure.’) To run the stored procedure, execute this command: EXEC TestInsertTrigger You see two messages that one row has been affected. To confirm that the trigger has executed, execute these commands: SELECT * FROM AuditedTable SELECT * FROM AuditTable Notice in Figure 13-7 that row 5 in the upper part of the results grid is the row inserted by the TestInsertTrigger stored procedure. Row 6 in the lower part of the results grid is the row inserted by the trigger into the AuditTable table. Figure 13-7: Confirming that the TestInsert Trigger stored procedure fires the trigger for an INSERT event. 225 Chapter 13: Using Triggers 226 Part III: Creating Databases and Database Objects with Transact-SQL Chapter 14 Creating Functions In This Chapter Introducing Transact-SQL functions Forming user-defined functions Changing and dropping functions Using system functions I n this chapter, I introduce you to SQL Server functions. I explain what a SQL Server function is, and I discuss the two types of user-defined func-tions supported in SQL Server 2005: a Transact-SQL function or a CLR func-tion created by a user who is using the CREATE FUNCTION statement. I show you how to create user-defined functions with Transact-SQL and show you how they can be used in your Transact-SQL code.

  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