Creating Triggers in SQL

6 Key excerpts on "Creating Triggers in SQL"

• 

  eBook - ePub

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

  eBook - ePub

  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

  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

  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