2017-3-16 TSQL BASIC programming stored procedure trigger cascade Delete

1. Define variables:

DECLARE @ variable name data type

Variable assignment:

SET @ variable name = value; SELECT @ variable name = value; (Common)

Value printing:

SELECT @ variable name print @ variable name

To perform variable assignment values, the code executes together, not the clause execution:

declare @aa int;
Select @aa = 11;
Select @aa;

Printing results:

2. Branch statements:

If @a>@b

Begin

Statement

End

Else

Begin

Statement

End

declare @a int;
declare @b int;
Select @a=1;
Select @b=2;
If @a>@b
Begin
Select ' A greater than B ';
End
Else
Begin
Select ' B is greater than a ';
End

Output: B is larger than a

3. Looping statements:

declare @a int;

Select @a=1;

while (@a<10)
Begin

Select @a

End　　

Declare @aa int;--defining variables
Select @aa =1;--Assignment
While @aa <10
Begin
Select @aa;--Print
Select @[email protected]+1;--assigns a value to a variable
End

4. Stored procedures:

Equivalent function, four elements, input, output, function body, functional name

Create proc Jiafa--No return value
@bb int,--the first argument
@cc the second parameter of int--
As
return @[email protected];

declare @bbb int; --Define a variable acceptance and
exec @bbb = Jiafa 10, 20; --2 Number Added
Select @bbb;--Print results

5. Triggers

A special stored procedure, no way to call it, but by adding and deleting the action to achieve

One action for a table can have only one trigger

Format:

Create trigger which table is that action

On table name----trigger for which table is written

For action----trigger after which action is triggered

or instead of----for which action to perform the replacement

As

Trigger content

Example:

--1. Create a trigger for the score table, and automatically query for the rest of the table after the content is deleted

Create Trigger Score_delete
On score
For delete
As
SELECT * FROM Score

Delete from score where sno= ' 103 '

--2 create a trigger for the score table, replace the content you want to delete into a query

Create Trigger Score_delete
On score
Instead of delete----instead of the content to be deleted replaced with the statement after as
As
SELECT * FROM Score

Delete from score where sno= ' 105 '

--3 set up a trigger for the score table, intercept the content to be deleted, and display the deleted content

Create Trigger Score_delete
On score
Instead of delete
As
SELECT * FROM deleted---deleted intercepts the contents of the trigger and will delete the contents of course number 105 to show it

Delete from score where sno= ' 105 '

--4 intercepts deleted data and makes reminders that cannot be deleted

Create Trigger Score_delete
On score
Instead of delete
As
declare @aa nvarchar (50); --Declaring variables
Select @aa = Sno from deleted; --Assign a value to the variable, assign the deleted sno to the variable
If @aa = ' 105 '
Begin
Select ' Cannot delete this data '--if it is 105 prompt this data cannot be deleted
End
Else
Begin
Delete from score where [email protected]; --if it is not restricted data can be deleted
End

Delete from score where sno= ' 105 '

6. Cascade Delete

Create 2 Table User table Class table
CREATE TABLE Users
(
IDS nvarchar (primary key),
Name nvarchar (50),
Class nvarchar (50)
)
CREATE TABLE Class
(
Classcode nvarchar () primary key,
ClassName nvarchar (50)

)
INSERT into class values (' c001 ', ' one Shift ')
INSERT into class values (' c002 ', ' Class two ')
INSERT into class values (' c003 ', ' Class three ')
INSERT into class values (' c004 ', ' Class four ')
INSERT into Users values (' 1 ', ' keyboard ', ' c001 ')
INSERT into Users values (' 2 ', ' mouse ', ' c002 ')
INSERT into Users values (' 3 ', ' Lie Triple ', ' c003 ')
INSERT into Users values (' 4 ', ' Zhao Si ', ' c004 ')

CREATE TRIGGER---cascade delete
Create Trigger Class_delete
On class
Instead of delete
As
declare @aa nvarchar (50);
Select @aa = Classcode from class;
Delete from users where [email protected];
Delete from class where [email protected];
This allows you to delete 004 of Classcode in the class table using a trigger.
Delete from Class where classcode= ' c004 '
SELECT * FROM Class

2017-3-16 TSQL BASIC programming stored procedure trigger cascade Delete