SQL Server trigger, Function

Create FUNCTION InsertComp (@ in varchar (30 ))
RETURNS varchar (30)
AS
BEGIN
Declare
@ Pos int,
@ Count int,
@ Result varchar (30 ),
@ Len int
Set @ result = @ in
-- The position before the point
Set @ pos = CHARINDEX ('.', @ result)-1
If (@ pos =-1) set @ pos = len (@ result)

Set @ count = @ pos-3
While (@ count> = 1)
Begin
Set @ len = len (@ result)-@ count
Set @ result = left (@ result, @ count) + ',' + substring (@ result, @ count + 1, @ len)
Set @ count = @ count-3
End
Return @ result
END
 

Trigger:

SQL internal, external, left, and right connections

In database applications, it is often necessary to query data from two or more tables, which requires connection query.
The format is as follows:
SELECT COLUMN_NAME, c o l u m n _ n a m e [,... n]
From ta B l e _ n a m e, ta B L E _ N A M E [,... n]
WHERE TABLE_NAME.COLUMN_NAME JOIN_OPERATOR ta B L E _ N A M E. C O L U M N _ N A M E
The above join operators (j o I N _ O P E R ATO R) can be: =,>, <,> =, <= ,! =, <> ,! > ,! <, = *, * =.
In a n s I, the "=" connection should be written as Inner Join; "* =" connection should be written as LEFT OUT JOIN; "= *" connection should be written as "RIGHT OUT JOIN ".
These write methods can be used in SQL SERVER. However, the expression specified by a n s I is an INNER JOIN sign. In general, in any connection, the "Primary Key = foreign key" connection query condition is used.
---------------------------------------------------------------------------------

Cross join is a query without the w h e r e clause. In mathematics, it is the Cartesian product of a table. If the R table and the S table are non-restricted connections, and the R table has X rows and the S table has Y rows, the result set is X * Y rows. That is, a row in the R table corresponds to all rows in the S table. In applications, unrestricted connections produce meaningless result sets, but they play an important role in the mathematical model of databases.
-------------------------------------------------------------------------
Inner join is also called inner join. Let's take a look at the following example:

SELECT PUB_NAME, TITLE
From titles, PUBLISHERS
Where titles. PUB_ID = PUBLISHERS. PUB_ID
Or written:
SELECT PUB_NAME, TITLE
FROM TITLES INNER JOIN PUBLISHERS
On titles. PUB_ID = PUBLISHERS. PUB_ID
Inner join is the default connection of SQL Server, which can be abbreviated as j o I N. Specify which tables are connected after j o I N. O n is followed by a specified connection condition.
The procedure is as follows:
1) retrieve a record that meets the requirements from the t I T L E S table.
2) scan the p u B l I S H E R S table and check whether each record is in the connection attribute.
3) if they are equal, information is displayed. Continue to take the next record from the table t I T L E S and repeat Step 2. In fact, to connect two or more tables, there must be a relationship between the primary key and the foreign key between these tables. Therefore, you only need to list the relationships of these keys to obtain the table join results. In the preceding example, p u B _ I d is the primary key of the p u B l I S H E R S table, p u B _ I d is the foreign key of the ti t l e s table. For more information, see p u B _ I d in the p u B l I S H E R S table. therefore, the join conditions for these two tables are t I T L E S. PUB_ID = PUBLISHERS. PUB_ID.
-----------------------------------------------------------------------
The outer join clause allows you to restrict rows in one table without limiting the rows in another table.
The following two examples describe the usage of external connections. Compare the two examples.
For example, show all titles (books without sales records are also included. "*" on the left indicates no limit on the number of tables on the left.
Select titles. TITLE_ID, title = convert (char (38), TITLE), QTY
From titles, SALES
Where titles. TITLE_ID * = SALES. TITLE_ID
Or write:
Select titles. TITLE_ID, title = convert (char (38), TITLE), QTY
FROM TITLES LEFT OUTER JOIN SALES
On titles. TITLE_ID = SALES. TITLE_ID

There are two external connection methods:
• A * = B includes all rows in table A of the first table, regardless of statement restrictions (for example,. t I t l e _ I d = B. t I t l e _ I d ).
It is equivalent to left out join.
• A = * B includes all rows in the second table B, regardless of statement restrictions (for example,. t I t l e _ I d = B. t I t l e _ I d ).
It is equivalent to right out join.
Create external connection rules:
1) The Outer Join shows all rows in the External table, including the rows that do not match the related table.
2) external connections can only be performed between two tables.
3) The is null search condition cannot be used in internal tables.

SQL server uses the Integrity Constraint Mechanism to Prevent invalid data from entering the base table of the database. If any DML execution result breaks the Integrity Constraint, the statement is rolled back and an error is returned. The integrity constraints implemented by SQL server fully comply with ANSI x3. 135-1989 and ISO9075-1989 standard.
 
Advantages of using integrity constraints to implement data integrity rules
Using integrity constraints to implement data integrity rules has the following advantages:
 
◆ When defining or changing tables, you can easily write programs and eliminate procedural errors without programming. Its function is controlled by SQL server. Therefore, descriptive integrity constraints are superior to application code and database triggers.
 
◆ The integrity constraints defined for a table are stored in the data dictionary. Therefore, the data entered by any application must comply with the integrity constraints associated with the table.
 
◆ Has the maximum development capability. When the transaction rules implemented by integrity constraints change, the Administrator only needs to change the definition of integrity constraints, and all applications automatically abide by the modified constraints.
 
◆ Because integrity constraints are stored in the data dictionary, database applications can use this information to provide immediate feedback before SQL statements are executed or before SQL server checks.
 
◆ Because the semantics of the description of integrity constraints are clearly defined, performance optimization can be achieved for each specified description rule.
 
◆ Because integrity constraints can be temporarily disabled, you can avoid constraints on the retrieval overhead when loading a large amount of data. When the database load is complete, the integrity constraints can be easily made available, and any new rows that compromise the integrity constraints are listed in the exception table.

SQL server DBAs and application starters can use the following integrity constraints for column value input:
 
◆ Not null constraint: if the value of a column in the table cannot be blank, you must specify the not null constraint in the column.

◆ UNIQUE code constraints: when duplicate values are not allowed for the specified columns or group columns in the table, you must specify the UNIQUE code integrity constraints for this column or group column. The column or group column in the UNIQUE code constraint definition is called a UNIQUE code. All Unique integrity constraints are implemented using the index method.

◆ Primary key constraint: Each table in the database can have a primary key constraint. The column or group column that contains the integrity constraints of the primary key is called as the master code. Each table can have a master code. SQL server uses indexes to implement the PRIMARY KEY constraint.

◆ Foreign key constraint (referred to as a reference constraint): In relational databases, tables can be associated with public columns. This rule controls the relationships between columns that must be maintained. A column or group column that is included in the definition of the reference Integrity Constraint is called a foreign code. A unique or square code in the table referenced by a foreign code is called a reference code. A table with external code is called a sub-table or a sub-table. The table referenced by the foreign code of the sub-table is called a parent table or a reference table. If the value of the external code of each row of the table must match the value in the primary code, you must specify the integrity constraints for reference.

◆ CHECK constraints: each row of a table must be TRUE or unknown to a specified condition. CHECK integrity constraints must be specified on one column or column group. If the condition of the CHECK constraint is calculated as FALSE when a DML Statement is issued, the statement is rolled back.

Use of triggers in SQL Server 2000

Trigger is a reusable tool in database applications and is widely used. I have written a statistical software for chemical data over the past few days, and I need to calculate the variance automatically based on sampling. Here, I used a trigger.

Below I have excerpted a text section about the trigger in the SQL Server official tutorial, which is a useful text description.

You can define a trigger that will be executed whenever the INSERT statement is used to INSERT data into the table.

When an INSERT trigger is triggered, new data rows are inserted into the trigger table and inserted Table. An inserted Table is a logical table that contains a copy of the inserted data rows. The inserted table contains the INSERT actions recorded in the INSERT statement. The inserted Table can also reference the log data generated by the initialization of the INSERT statement. The trigger checks the inserted Table to determine whether to execute the trigger action or how to execute it. Rows in the inserted Table are always copies of one or more rows in the trigger table.

The log records all data modification actions (INSERT, UPDATE, and DELETE statements), but the information in the transaction log is unreadable. However, the inserted Table allows you to reference the Log changes caused by the INSERT statement, so that you can compare the inserted data with the changes to verify them or take further actions. You can also directly reference the inserted data without storing them in variables.

Example

In this example, a trigger is created. This trigger updates a column (UnitsInStock) in the Products Table whenever a product is ordered (insert a record to the Order Details table at any time ). The original value minus the purchased quantity is the new value.
USE Northwind
Create trigger OrdDet_Insert
ON [Order Details]
FOR INSERT
AS
UPDATE P SET
UnitsInStock = P. UnitsInStock-I. Quantity
FROM Products as p inner join Inserted AS I
On p. ProductID = I. ProductID

Working Process of the DELETE trigger

When the DELETE trigger is triggered, rows deleted from the affected table are placed in a special deleted table. The deleted table is a logical table that retains a copy of the deleted data row. The deleted table can also reference log data generated by the initialization DELETE statement.

When using the DELETE trigger, consider the following items and principles:

• When a row is added to the deleted table, it no longer exists in the Database Table. Therefore, the deleted table and the database table do not have the same rows.

• When creating a deleted table, the space is allocated from the memory. The deleted table is always stored in the cache.

• The trigger defined for the DELETE action does not execute the truncate table statement because the log does not record the truncate table statement.

Example

In this example, a trigger is created to update the Discontinued column in the Products Table whenever a product category is deleted (that is, a record is deleted from the Categories table. All affected products are marked as 1, indicating that these products are no longer used.
USE Northwind
Create trigger Category_Delete
ON Categories
FOR DELETE
AS
Update p set Discontinued = 1
FROM Products as p inner join deleted AS d
On p. CategoryID = d. CategoryID

UPDATE trigger procedure

The UPDATE statement can be regarded as a two-step operation: the DELETE statement that captures the before image and the INSERT statement that captures the after image. When an UPDATE statement is executed on a table with a trigger defined, the original row (front image) is moved to the deleted table, and the updated row (Back image) is moved to the inserted Table.

The trigger checks the deleted table, inserted Table, and updated table to determine whether multiple rows are updated and how to execute the trigger action.

You can use the if update statement to define a trigger to monitor data updates of a specified column. In this way, the trigger can easily isolate the activity of a specific column. When it detects that the specified column has been updated, the trigger will further execute the appropriate action. For example, an error message indicates that the column cannot be updated, or execute a series of action statements based on the new updated column value.

Syntax
If update (<column_name>)

Example 1

In this example, the user is prevented from modifying the employee ID column in The Employees table.
USE Northwind
GO
Create trigger Employee_Update
ON Employees
FOR UPDATE
AS
If update (EmployeeID)
BEGIN
RAISERROR ('transaction cannot be processed .\
* *** Employee ID number cannot be modified. ', 10, 1)
ROLLBACK TRANSACTION
END

Working process of the instead trigger

You can specify the instead of trigger on a table or view. Executing this trigger can replace the original trigger action. The instead of trigger extends the type OF view update. For each trigger action (INSERT, UPDATE, or DELETE), each table or view can have only one INSTEAD trigger.

The INSTEAD trigger is used to update views that cannot be updated normally. For example, you cannot DELETE a connection-based view. However, you can write an instead of delete trigger to DELETE the object. The above trigger can access the data rows that have been deleted when the view is a real table. Store the deleted rows in a worksheet named deleted, just like the AFTER trigger. Similarly, in the update instead of trigger or insert instead of trigger, you can access the new row in the inserted Table.
You cannot create an instead of trigger in a view defined by with check option.

Two SQL Server triggers
-- When "Shipping table _ details" is added, deleted, or changed, the "Order table _ details" is used to modify the number of deliveries and the number of outstanding deliveries.
Create trigger tr _ shipping table _ details
On shipment table _ details
For DELETE, INSERT, UPDATE
As
Declare @ JiaoHuo varchar (20)
Begin
Set @ JiaoHuo = (Select Sum (delivery quantity) from shipping table _ detail Group by no. Having no. = (Select No. From Deleted ))
Set @ JiaoHuo = ISNULL (@ JiaoHuo, (Select Sum (delivery quantity) from shipping table _ detailed Group by no. Having no. = (Select No. From Inserted )))
Set @ JiaoHuo = ISNULL (@ JiaoHuo, 0)
Update n set delivered quantity = @ JiaoHuo, undelivered quantity = N. purchased quantity-@ JiaoHuo
From Order table _ details as n inner Join Deleted AS D
On n. No. = D. No.
End

-- When "Order table _ details" is added, deleted, or changed, determines whether the total number of undelivered orders is 0, change the order status of the "Order table" to "completed"; otherwise, "incomplete"
Create trigger tr_Status
ON Order table _ details
For insert, UPDATE, DELETE
AS
Declare @ Order varchar (20)
Begin
Set @ Order = (Select Order number from Deleted)
Set @ Order = ISNULL (@ Order, (Select Order number from Inserted ))
IF (Select Sum (undelivered quantity) From Order table _ detail Group by Order No. Having Order No. = @ Order) = 0
Begin
UPDATE Order table Set Order status = 'finished ', completion date = GetDate () Where Order number = @ Order
End
Else
Begin
UPDATE Order table Set Order status = 'unfinished ', completion date = NUll Where Order number = @ Order
End
End

Query Language (SQL) Functions
The SQL function executes a list of any SQL queries and returns the results of the last query in the list. It must be a SELECT statement. return the first row of the last query result in a relatively simple case (not a set. (Remember that the "first line" of multi-row results is not clear unless you use order by to sort the results .) if the last query does not return rows, NULL is returned.
In addition, an SQL function can be declared as returning a set by declaring the return type of the function as SETOF sometype. at this time, all rows of the last query result will be returned. more details are described below.
The SQL function body should be a list of one or more SQL statements separated by semicolons. note that the syntax of the create function command requires that the FUNCTION body be enclosed in single quotes. Therefore, the single quotes (') used in the FUNCTION body must be escaped by writing two single quotes (') or put a backslice (\ ') before the single quotation marks to escape (\').
You can use the $ n syntax to reference SQL function parameters: $1 refers to the first parameter, $2 refers to the second parameter, and so on. If the parameter is of the composite type, you can use the dot notation, for example, "$ 1.emp", to access the fields in the parameter.
12.2.1. Example
Let's take a look at the following simple SQL function example, which will be used to deduct a bank account (debit consumption debit:
Create function tp1 (integer, numeric) RETURNS integer'
UPDATE bank
SET balance = balance-$2
WHERE acctountno = $1;
SELECT 1 ;'
LANGUAGE 'SQL ';

A user can use this function to charge account 17 $100.00 as follows:
SELECT tp1 (17,100.0 );

In fact, we may like the function to have a result that is more useful than the constant "1", so the more likely definition is
Create function tp1 (integer, numeric) RETURNS numeric'
UPDATE bank
SET balance = balance-$2
WHERE accountno = $1;
SELECT balance FROM bank WHERE accountno = $1;
'Language SQL;
It modifies the balance and returns a new balance.
Any command set in SQL can be packaged into a function. these commands can contain data modifications (that is, INSERT, UPDATE, and DELETE) and SELECT queries. however, the final command must be a SELECT statement of the return type declared by the return function.
Create function clean_EMP () RETURNS integer'
DELETE FROM EMP
Where emp. salary <= 0;
SELECT 1 AS ignore_this;
'Language SQL;

SELECT clean_EMP ();
X
---
1
12.2.2. Basic SQL Functions
The simplest SQL function may be a function without parameters, but returns an integer of the basic type:
Create function one ()
RETURNS integer
AS 'select 1 as RESULT ;'
LANGUAGE 'SQL ';

SELECT one ();
One
-----
1

Note that the target column (named RESULT) is defined for the function, but the target column of the query statement that activates the function overwrites the target column of the function. Therefore, the RESULT is marked as one rather than RESULT.
Defining SQL functions with the basic type as parameters is almost the same. note how to use the parameters $1 and $2 in the function:
Create function add_em (integer, integer)
RETURNS integer
AS 'select $1 + $2 ;'
LANGUAGE 'SQL ';

SELECT add_em (1, 2) AS answer;

+ ------- +
| Answer |
+ ------- +
| 3 |
+ ------- +

12.2.3. Composite SQL Functions
When the declared function uses a composite type as a parameter, we need to declare not only the parameter we need (like $1 and $2 above), but also the parameter field. for example, suppose that EMP is a table that contains employee information, and is also the name of the compound type of each row in the table. here is a function double_salary, which calculates the value after your salary doubles:
Create function double_salary (EMP) RETURNS integer'
SELECT $1. salary * 2 AS salary;
'Language SQL;

SELECT name, double_salary (EMP) AS dream
FROM EMP
Where emp. cubicle ~ = Point' (2, 1 )';
Name | dream
------ + -------
Sam | 1, 2400
Note that $1. salary syntax is used to SELECT a field for the value of the parameter row. Also, note how the SELECT command uses the name of a table to represent the entire row of the table as a composite value.
We can also write a function that returns the composite type. (However, we will see below that there are some unfortunate restrictions on the use of these functions .) the following is an example of returning a row of EMP functions:
Create function new_emp () returns emp'
SELECT text ''none' AS name,
1000 AS salary,
25 AS age,
Point ''()'' AS cubicle'
LANGUAGE 'SQL ';
In this example, we assign a constant to each field. Of course, we can use any calculation or expression to replace these constants. Pay attention to the two important problems of defining such a function:
• The Order of the target list must be exactly the same as that of the fields in the table associated with the composite type.
• You must convert the expression type to match the definition of the composite type. Otherwise, you will see the following error message:
ERROR: function declared to return emp returns varchar instead of text at column 1
There are some unpleasant restrictions in the current PostgreSQL version. These restrictions constrain the way we can use compound type return values. simply put, when calling a function that returns a row, we cannot retrieve the entire row. we either map a field out of the row or pass the whole row to another function. (trying to display the value of the entire row produces meaningless numbers .) for example,
SELECT name (new_emp ());
Name
------
None
This example uses the function concept for field extraction. The simple method to explain these problems is that we usually use the attribute (table) and table. attribute concepts interactively:
--
-- The following is the same as this sentence:
-- Select emp. name AS youngster from emp where emp. age <30
--
SELECT name (EMP) AS youngster
FROM EMP
WHERE age (EMP) <30;
Youngster
-----------
Sam
Generally, we must use the function syntax ing as the field returned by the function because when it is joined with the function call, the analyzer cannot understand the point-to-point syntax for ing.
SELECT new_emp (). name AS nobody;
ERROR: parser: parse error at or near "."
Another way to return row results using a function is to declare another function. This function accepts a row type parameter and then passes the function result to the second function:
Create function getname (emp) RETURNS text
'Select $1. name ;'
Language SQL;
SELECT getname (new_emp ());
Getname
---------
None
(1 row)
12.2.4. Return the SQL functions of the Set
As mentioned above, an SQL function can be declared as returning SETOF sometype. at this time, the final SELECT query of the function is executed till the end, and each row output by the function is returned as an element of the set.
The function that returns the set can only be called in the target list of a SELECT query. each row generated by SELECT calls the function of the returned set, and generates an output row for each element of the result set of the function. for example:
Create function listchildren (text) returns setof text
'Select name FROM nodes WHERE parent = $1'
Language SQL;
SELECT * FROM nodes;
Name | parent
----------- + --------
Top |
Child1 | Top
Child2 | Top
Child3 | Top
SubChild1 | Child1
SubChild2 | Child1
(6 rows)

SELECT listchildren ('top ');
Listchildren
--------------
Child1
Child2
Child3
(3 rows)

SELECT name, listchildren (name) FROM nodes;
Name | listchildren
-------- + --------------
Top | Child1
Top | Child2
Top | Child3
Child1 | SubChild1
Child1 | SubChild2
(5 rows)
In the final SELECT statement, note that there are no rows such as Child2 and Child3. This is because listchildren returns an empty set for these input, so no output rows are generated.