2014-12-04 views, triggers

View : Treat a query result set as a table

Example: SELECT * FROM (SELECT * from Score,grade where score.degree between Low and UPP) as Jieguotable

where rank= ' A '

as a virtual table after as

CREATE VIEW SHITU1

As

SELECT * FROM Student,score where Student.sno=score.sno

Go

As and go are actually tables connected, the columns in the table cannot be coincident

View can only query use, can not delete data, the equivalent of saving a piece of data in

Trigger: A special stored procedure that is raised by adding or deleting a database.

Mouse actions: a database → programmability → database triggers

Example: Create TRIGGER Tr_student_insert---(Naming rules: TR is a trigger, which table is student, Insert is triggered when a new action is added)

On student---which table is acting on

For inserts------for is a new trigger, and for can also be written after

As

Begin

SELECT * FROM Student

End

Go

INSERT into student values (' 303 ', ' xiaoming ', ' Male ', ' 1992-07-21 ', ' 95033 ')

-----After adding the above data, trigger the query

Replace:

Create Trigger Tr_student_delete
On student
Instead of delete
As
Begin
DECLARE @sno int
Select @sno =sno from deleted
Delete from score where [email protected]
Delete from student where [email protected]
End
Go
Delete from Student where sno=304
Select *from Student
INSERT into score values (304, ' 3-105 ', 98)

Deleted is temporarily deleted, containing the contents of the delete action you will be performing

Insert for temporary increase

Create Trigger Tr_student_inserted
On student
Instead of insert
As
Begin
declare @sno int, @sname varchar, @ssex varchar (20),
@sbirthday datetime, @class varchar (20)
Select @sno =sno, @sname =sname, @ssex =ssex, @sbirthday =sbirthday, @class =class from inserted
If @ssex = ' Male '
Begin
Set @ssex = ' 1 '
End
INSERT into student values (@sno, @sname, @ssex, @sbirthday, @class)
End
Go
INSERT into student values (303, ' Billiton ', ' Male ', ' 1992-07-21 ', ' 95033 ')
Select *from Student

2014-12-04 views, triggers