SQL storage and triggers, SQL storage triggers

SQL storage and triggers, SQL storage triggers

Stored Procedures: Same as functions
Stored in the database -- programmable -- Stored Procedure

Create a stored procedure:
Create proc JiaFa
-- Required Parameter
@ A int,
@ B int
As
-- Stored procedure content
Declare @ c int;
Set @ c = @ a + @ B;
Return @ c;
Go

Public int JiaFa (int a, int B)
{
Int c = a + B;
Return c;
}
-- After the execution is complete, select all and execute the creation

Execute the stored procedure:
Exec JiaFa 3,5;

Declare @ f int;
Exec @ f = JiaFa 3, 5;
Print @ f;

Example:

-- Query the number of vehicles that meet the condition in the vehicle table based on the input parameters.
Create proc ChaXun
@ N varchar (20)
As
Declare @ num int
Select @ num = count (*) from car where name like '%' + @ n + '%'
Return @ num
Go

Declare @ m int
Exec @ m = ChaXun 'audi'
Print @ m

 

Trigger:
Is a special stored procedure;
It is triggered by adding, deleting, modifying, and so on. There is no parameter and no return value;

Create trigger Insert_Student -- naming convention
On student -- target table
For insert -- for which action is triggered

-- Onclick = "show ()"

As
Code segment that triggers execution
Go

----------------------------------------------------
Create trigger Delete_Info
On info
Instead of delete
As
Declare @ c varchar (20)
Select @ c = code from deleted

Delete from work where infocode = @ c
Delete from family where infocode = @ c
Delete from info where code = @ c
Go

Create trigger Delete_Nation
On nation
For delete
As

Go

1. for is triggered after the action is executed.
2. instead of delete indicates that it is triggered before deletion. It can be understood as an alternative. After writing this, the code to be executed will be useless and will be overwritten by the trigger code.

Trigger is commonly used for cascading deletion:
Create trigger delete_student
On student
Instead of delete
As
-- If you want to delete the student table data, you need to cascade the deletion.
Declare @ sno varchar (20 );
Set @ sno = sno from deleted -- deleted is in a fixed format. to delete the data that can be deleted by the execution, the data is not deleted, but displayed, obtain the sno of the data to be deleted, and then delete the data of this sno from other tables.
Delete from score where sno = @ sno;
Delete from student where sno = @ sno;
Go