Database Design (7/9): Triggers

Is it completely new for designing and creating a database? It doesn't matter, Joe Celko, one of the most-read SQL authors in the world, will tell you these basics. As always, even the most professional database veteran will surprise them. Joe is Dmbs magazine, the most popular author of the book for many years. He teaches SQL in the United States, Britain, Northern Europe, South America and Africa. He worked for the Ansi/iso SQL Standards Committee for 10 years and made outstanding contributions to the SQL-89 and SQL-92 standards.

In the 1th to 4th, we created the table, the foundation and visualization of the architecture. The 5th and 6th articles are about stored procedures. This article will describe the features you need to avoid on triggers.

Usually you will be told that triggers are "special categories" of stored procedures, but this is not entirely right. It has no parameters, you cannot trigger it, it has no local pseudo-table (locally pseudo-tables) elsewhere. Before we discuss it in detail, we stop and discuss the SQL model that modifies the data in the underlying table.

Insert,update and delete

SQL is a collection-oriented language, so it modifies tables with data collections at the same time. It is not a record-oriented language, it modifies one record at a time to process the data, in order (think of the tape and punch card). The 3 basic operations for modifying the contents of a table are insert,update and delete.

Insertions are the easiest to understand. This is a conceptual model, not an actual implementation. You put a rowset into an existing table. But more than that. This set is used once as the entire unit, so any function call with a constant such as Current_timestamp has the same value for all new rows. Identity table Nature (nature, not column), which violates this model by querying rows and attempting to write when table-level counters are added. The identity value uncertainty depends on the index at insertion and the physical state of the mechanism.

In the Ansi/iso Standard and T-SQL INSERT, the new row is saved in the pseudo-table and is named new. If an insert introduces a table to a state that violates any constraints, the transaction is rolled back and you receive an error message. That means that the new row will never be inserted into the table (however, the identity property will increase).

The deletion is similar. In the Ansi/iso Standard and T-SQL Delete, all rows in the base table that conform to the WHERE clause of the DELETE from statement are placed in the pseudo-table and are named old. If the deletion introduces the table to a state that violates any constraints, then the transaction is rolled back and you receive an error message. That means that the old line never leaves the table.

In fact, most SQL engines mark old rows in one pass, check constraints in the last pass, and remove them.

Updates are combinations of deletions and insertions. The conceptual model is that we find and create a delete pseudo-table on the WHERE clause, just like a delete from. Then we look at the SET clause to create a new pseudo-table to insert.

A table is made up of rows, and rows are made up of columns. Each assignment is completed at the same time in the SET clause. If a column is missing from the SET clause, the SQL engine actually creates a "set < column name >=< column name >"-Assignment that does nothing. This is not handled as a procedural language from left to right. This statement swaps values in columns A and B, and completes at once.

UPDATE Foobar SET = B,      = A;

This process statement sets column A and column B to the same value because it executes from left to right.

BEGIN SET =  SET=END;

The delete line is removed, the insert row continues in the table, and all is completed at once. If an update introduces a table to a state that violates any constraints, the transaction is rolled back and you receive an error message. That means that the table is never modified.

Database Events

A trigger is code that is attached to one and only one table. But a table can have multiple triggers. There is a CREATE TRIGGER statement because it is a persistent schema object. The basic T-SQL syntax is straightforward:

CREATE TRIGGER <TriggerName> on <Table or ViewName>{ for |After|INSTEAD of} {[INSERT] [,] [UPDATE] [,] [DELETE]} as<TriggerBody>;

Trigger name and table name or view name are the corresponding object names. After and for are equivalent keywords, but after has more descriptive rows (other SQL before triggers). We will explain the instead of options later.

Insert,update,delete is called a database event or action. The SELECT statement, drop and alter are not database events. When one of these operations is completed on the table, the trigger is triggered when the database action is completed successfully. For each database event, the code in the trigger body executes once at the surface level.

Attention. Your program updates the table T1, which triggers the trigger TR1, which updates the table T1 itself. Because the table T1 is updated, trigger TR1 is triggered again, so it goes bad. There may be no rest. But it doesn't need to stop there. Your trigger can cause triggers on other tables to trigger. Triggers can also be embedded. Assuming that the program updates table T1, trigger TR1 is triggered, and it updates the table T2. The trigger on the T2 triggers, and the table T1 is updated again. This pattern can be extended to multiple tables without stopping the loop.

The SQL engine needs to keep track of all these changes so that if something goes wrong, it can roll back. Avoid writing this code: it consumes resources, runs slowly, and locks down the entire database. It is also difficult to maintain.

Trigger Body

The trigger body is a block of T-SQL procedural statements. But it's a little different. You can't wear parameters to the trigger body, like a stored procedure. The trigger body accesses the inserted and deleted pseudo-tables. You can rename these pseudo-tables if you want.

This format also has special logic functions: UPDATE (< column name >) and columns_updated (). These tests see if an update from or INSERT INTO statement modifies one or more columns in their parameter list. This is a proprietary feature, so I'll explain it in more detail, and a brief example would be:

CREATE TRIGGER No_embezzlement_trigger  on  UpdateasIFupdate(payroll_amt)BEGIN  RaiseError ('youcannot give yourself a raise'); ROLLBACK TRANSACTION ; END;

INSTEAD of Triggers

The updated view is known as a NP-complete problem. In English, that means that we know that there is no conventional way to accomplish it in a reasonable number of times, as the problem becomes bigger and larger.

The INSTEAD of triggers is the algorithm restriction that we update the view and circumvent the RDBMS. A nstead of trigger executes the trigger body, not the database action it triggers. This is easier to explain with examples. Let's say we have a view defined with join and aggregate functions on two tables:

 create  view   Salessummary (ORDER_NBR, Order_amt_tot)  as  select  O.ORDER_NBR, O.customer_name, sum  (D.unit_price *   D.order_qty)  from  Orders as  O, order_details as   D  where  o.order_nbr =   D.ORDER_NBR  group  by  O.order_ NBR; 

If there is a trigger on the Salessummary database event, it will fail if you do not use the instead of trigger. This view has joins, calculations, and aggregations to ensure that it is not updatable. But instead OF triggers is not a front trigger! A true pre-trigger executes its code and then attempts to complete the database event. The INSTEAD of trigger executes its code on its own and completes it.

Remember that the view is virtual: it does not exist physically and continuously. That means you don't have deleted and inserted pseudo-tables to use. Further, you cannot get parameters from the insert Into,delete from or UPDATE statement. All code needs to be manipulated on the underlying table.

INSTEAD of triggers can also run on the underlying table as the view. Usually it doesn't work like this. T-SQL is accustomed to having after triggers, checking for results and corrections, or rolling back if there is a problem.

Audit triggers

A common technique for using triggers is to collect audit data in one or more tables. It is often thought that this is not a good idea. It reduces the performance of the application by adding additional reads and writes. There are no triggers for the SELECT statement, so you cannot track who is viewing the data. The Health Insurance Portability and Accountability Act (HIPAA (Insurance Portability and Accountability Act)) and many other laws require a record of each data being viewed.

But more than that, the basic principle of auditing is that audits are separated from the things that are audited. For example, when a purchase order is created for a shipment, the person responsible for transporting it and agreeing to the order is not the same person. The temptation to ship to your own avoids this method.

Assume that the table has a user ID for the column user event date and employee update record, which is done by the trigger. It doesn't sound good until you think about it. The trigger is always there and will not be overturned. Oh! If you delete a row, the audit data is also deleted. If someone has access to the audit column, they will be updated to any value.

The third-party audit tool uses transaction log files, which are created by the server for recovery and network bandwidth to capture all the actions that need to be audited, to keep the data safe and physically separate from the remaining databases. This will pass the law test. Remember that ROI in today's United States says "the risk of imprisonment (Risk of incarceration)", not "ROI (return on Investment)".

data and referential integrity triggers

If you have the old SQL code migrated to the newly released SQL, you can see if there are any triggers that can be replaced with DRI (referential integrity) constraints and actions. This is the original intention of the trigger. For example, when an order is deleted in the order form, the trigger deletes all related records in the order list. Can ask seniors when we forget triggers or use them wrong, how much time spent looking for unrelated lines.

Now, most of these integrity triggers can be replaced by declaring DRI operations. They perform simple actions on the delete and update actions. This action is an OPTION clause on the DDL. The complete syntax is:

 foreign  key  (<  referencing column  List>  )  references  <  referenced table  name>  (<  referenced table  Span style= "color: #0000ff;" >column  list>  )  [ on UPDATE | On DELETE  ][ no ACTION | CASCADE | SET NULL | SET DEFAULT   " 

No action: An error message tells the user that this operation is not allowed and we get a rollback.

CASCADE: Delete or update all related row data in the foreign key relationship.

Set NULL: Sets the reference column to null. This assumes that for tables, all foreign key columns allow NULL to be accepted.

SET defaults: This is the default value referenced by the table column definition. This assumes that for a table, all columns have a defined default value.

Use triggers only when integrity rules are complex. One I can think of, when a member is inserted or deleted, in more than one group involves the example of a re-release of values. Even then, consider putting it into a stored procedure.

T-SQL also has an extension to DDL triggers. These are triggered by DDL events, not DML events--create, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE statistics statements .

multiple triggers for the same event

It is legal to have more than one trigger for the same database event. It's not a good idea, but it's legal in T-SQL. Try to do it in a trigger, which is easy to maintain.

By default, multiple triggers for the same operation are indeterminate in the SQL Server table. However, it is possible to declare the firing order with the system stored procedure Settriggerorder for two after triggers. This stored procedure cannot be used on instead OF triggers.

The syntax is very straightforward:

EXECsp_settriggerorder@triggername = <Explains itself>,@order = [first| Last| NONE],--Firing Order@stmttype = [insert| update| DELETE],--Trigger Type@namespace = [database| server| NULL];--explains itself

The parameter @order indicates whether the first or most trigger is triggered. If none is specified, then there is no enforcement order and we return to the default state. Obviously you can't have 2 first or 2 last triggers.

However, if you have a 3rd trigger, it is not first or last, it must be in the middle of the trigger sequence.

Summary

Gentlemen, more than T-SQL, there is a proprietary implementation of the trigger. Therefore, they do not migrate. Some of their behavior can be non-deterministic, so they are difficult to debug. They do not tell the optimizer what it can use, like the DRI action. However, you will probably find that they are the safest way to confirm the complete line rules for complex data. Oh, my God!

original link

http://www.sqlservercentral.com/articles/Stairway+Series/71822/

Database Design (7/9): Triggers