SQL Server triggers, views

I. Flip-flop

1. Triggers are special types of stored procedures that automatically take effect when language events are executed. SQL Server includes three general types of triggers: DML triggers, DDL triggers, and logon triggers.

There are two main types of DML triggers, DML triggers: After (for), INSTEAD of triggers, and DML triggers using deleted and inserted logic (concept) tables. They are structurally similar to the table that defines the trigger, which is the table on which the user action was attempted. The deleted and inserted tables save old or new values for rows that may be changed by the user.

• For insert operations, inserted retains new records, deleted no records
• For delete operations, inserted no records, deleted retains deleted records
• For the update operation, inserted retains the modified record, deleted retains the pre-modified record

2. Disable statements for all triggers
ALTER TABLE student DISABLE trigger All
--Enable statements for all triggers
ALTER TABLE student enable trigger all
--If you know the name of the trigger, you can change all to the name of the trigger you want to turn off or enable

CREATE trigger Tr_student_delete--Creating a Delete trigger
on student--for that table
instead of delete--replace DELETE statement
as
INSERT into student values (' Dragon ', ' Male ', ' class three ', 1007,1008,1009)
Go
--Executes a DELETE statement for the student table
Delete from student where code =4
--triggers the Tr_student_delete trigger when executing the above statement
--because the inside is instead of this DELETE statement that I executed
--so the data for code 4 is not deleted
--and added a new line of data

CREATE trigger Tr_student_delete2--Creating a Delete trigger
on student--for that table
for delete--executes the statement inside the trigger after an external DELETE statement is executed
as--is the same as after
INSERT into student values (' Hyun ', ' Female ', ' class two ', 1004,1005,1006)
Go
--Execute DELETE statement
Delete from student where code =30
--Execution of the DELETE statement on the student table when a trigger is found
--Check to see if it's for or after
--The statement to execute is executed first
--then execute the statement inside the trigger

--Execute Delete a piece of data,
--use deleted to represent the deleted data, from which you can get the value
CREATE TRIGGER Tr_teacher_delete
On teacher
instead of delete
as
declare @code int
Select @code = (select code from deleted)
Update teacher Set name = ' Andy Lau ' where code = @code
Go
--Execution
Delete from teacher where code = 1005
--The above statement should be executed before the trigger, so use deleted to represent the data
--You can get any column in this row of data in the trigger
-can be taken directly to use

--insert adds a piece of data, inserted represents the newly added data,
--Get the teacher number from the
--and according to the teacher number to see is lesson is music, if it is music into language.
CREATE TRIGGER Tr_teacher_insert
On teacher
For Insert
as
declare @code int
Select @code = (select code from inserted)
declare @lesson char (TEN)
Select @lesson = lesson from teacher where code [email protected]
If @lesson = ' Music '
Update teacher Set lesson = ' Language ' where code [email protected]
Go
--Execution
INSERT into teacher values (' Alan Tam ', ' Music ' , ' 1950-04-05 ')

--For insert operations, inserted retains new records, deleted no records
--For delete operations, inserted no records, deleted retains deleted records
--For the update operation, inserted retains the modified record, deleted retains the pre-modified record

Two. View

1.select* from Cangku
SELECT * FROM Gongying
--Write the above two statements as a statement display
Select Cangku.ccode,cname,cprice,cshu,cgcode,gongying.gname,gtel from Cangku, gongying where cangku.cgcode= Gongying.gcode
--join on
Select Cangku.ccode,cname,cprice,cshu,cgcode,gongying.gname,gtel from Cangku join gongying on cangku.cgcode= Gongying.gcode

2. View creation (statement to create a view)
CREATE view cangkugongying--syntax for creating views
As--as go in the middle put a query statement I need
Select Cangku.ccode,cname,cprice,cshu,cgcode,gongying.gname,gtel from Cangku join gongying on cangku.cgcode= Gongying.gcode
Go
--Call View
The Select *from cangkugongying--view is a virtual table, so you can

3. View creation (mouse action create VIEW)

Right-click on the view to select New View,

On the page that pops up, select the name of the table you want to relate to.

Select the name of the desired column and save it.

Once saved, you can invoke the view directly using the statement. The view is a virtual new table.

SELECT * from Xin
Select Cname,cshu from Xin

SQL Server triggers, views