Use of SQL Server triggers

Trigger createdCode

Create Trigger Tg_projectname
On Table1
After Update
As
Update Table 2
Set [ Project name ] = B. Project Name
From Table2 A, inserted B
Where A. projid = B. ID

Description of inserted and deleted in the trigger.

Inserted Trigger Two special tables are used in the statement: deleted table and inserted Table. Microsoft & reg; SQL Server 2000 automatically creates and manages these tables. You can use these two temporary tables with resident memory to test the effects and settings of some data modifications.Trigger Operation Conditions; however, you cannot directly change the data in the table.
Inserted and deleted tables are mainly used Trigger Medium:
Extends the integrity of reference between tables.
Insert or update data in a base table based on a view.
Check for errors and take actions based on the errors.
Locate the table status difference before and after data modification, and take action based on the difference.
The deleted table is used to store Delete and Update Statement. When you execute delete or Update Statement Trigger Delete the table and transfer it to the deleted table. Deleted table and Trigger The table usually does not have the same rows.
The inserted Table is used to store insert and Update Statement. In an insert or update transaction, the new row is added to the inserted Table and Trigger Table. The rows in the inserted Table are Trigger Copy of the new row in the table.
The update transaction is similar to executing Insert after deletion. First, the old row is copied to the deleted table, and then the new row is copied Trigger Table and inserted Table.
In settings Trigger The condition must be Trigger Use the inserted and deleted tables properly. Although the deleted table referenced during the insert test or the inserted Table referenced during the delete test will not cause any errors, in this caseTrigger The test table does not contain any rows.
Description if Trigger The operation depends on the number of rows affected by a data modification and should be modified for multiple rows (insert, delete, or Update ) Use the test (for example, check @ rowcount) and then take corresponding countermeasures.
SQL Server? 2000 after Trigger References text, ntext, or image columns in the inserted and deleted tables. However, instead Trigger Reference these columns. For more information, see create trigger.
In instead Trigger Using inserted and deleted tables
Passed to the table-defined instead Trigger The inserted and deleted tables of follow and pass to the After Trigger The same rules for the inserted and deleted tables. The formats of inserted and deleted tables are as follows: Trigger The format of the table is the same. Each column in the inserted and deleted tables is directly mapped to a column in the base table.
References with instead Trigger Insert or Update When the statement must provide column value rules and the table does not have instead Trigger Same:
You cannot specify a value for a computed column or a column with a timestamp data type.
The value cannot be specified for a column with the identity attribute, unless the identity_insert of this column is on. When identity_insert is on, the insert statement must provide a value.
The insert statement must provide a value for all not null columns without the default constraint.
For any column except the calculation column, identification column, or timestamp column, the values of any column that allows null values or not null columns with default definitions are optional.
When insert, Update Or the delete statement references Trigger The Database Engine calls Trigger Instead of taking any direct actions on any table. Even if the information formats in the inserted and deleted tables generated for the view are different from those in the base table Trigger When generating any statements required to execute the request operation in the base table, you must still use the information in the inserted and deleted tables.
Passed to the instead of defined on the ViewTrigger The inserted and deleted table types of match the SELECT statement list defined for this view. For example:
Create view employeenames (employeeid, lname, fname)
As
Select employeeid, lastname, firstname
From northwind. DBO. Employees
The view result set has three columns: an int column and two nvarchar columns. Passed to the instead of defined on the View Trigger The inserted and deleted tables also have the int column named employeeid, The nvarchar column named lname, And the nvarchar column named fname.
The view selection list also contains expressions that are not directly mapped to a single base table column. Some view expressions (such as constant calls or function calls) may not reference any column, and such expressions will be ignored. Complex expressions reference multiple columns, but in inserted and deleted tables, each inserted row has only one value. If a simple expression in a view references a computed column with a complex expression, these simple expressions have the same problem. Instead Trigger These types of expressions must be processed. For more information, see instead Trigger Expression and calculation column in.
By the way, when you create a table Trigger Then, we will discuss it in three cases.
1. Insert)
The inserted Table has data, and the deleted table has no data.
2. Delete)
The inserted Table has no data, and the deleted table has data.
3. Update operations ( Update )
The inserted Table has data (new data), and the deleted table has data (old data)