The result set--select query can be used as an alias as a virtual table with Select*from (Select*from score, grade where score.degree between Low and UPP) as Jibieta ble where rank= ' A '--create view (view can only be based on entity table) CREATE VIEW SHITU1 as select Student.sname,student.sno,score.degree from Stude Nt,score where Student.sno=score.sno go create view shitu2 as Select*from (Select*from score, grade where Score.degree bet Ween Low and UPP) as jibietable where rank= ' A ' Go--modify view Alter view SHITU2 as SELECT *from Student go select *from shitu2 --Delete Views Drop view SHITU1
--Triggers---special stored procedures. Change the database table by adding and deleting to automatically throw ALTER trigger Tr_student_insert on student--which table on which to establish the trigger for Insert--for is added after the trigger, for can also be written after as begin UPDA Te student set sname= ' Yang Bo ' where sname= ' Billiton ' end go insert into student values (304, ' Yang Yue ', ' Male ', ' 1990-07-21 ', ' 95033 ') Select *FR Om Student
--Alter trigger tr_student_delete on Student instead of the Delete as BEGIN declare @sno int select @sno =sno from deleted Delete from score where [e-mail protected] delete from student where [email protected] end Go Delete fro M Student where sno=304 select *from Student INSERT INTO score values (304, ' 3-105 ', 98)
--Drop trigger Tr_student_insert CREATE trigger tr_student_inserted on Student instead of insert as BEGIN declare @sno I NT, @sname varchar, @ssex varchar, @sbirthday datetime, @class varchar 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
--Transaction Select *from HUOWU begin tran--Transaction--Start the Write process statement
--After the statement has been written, if @ @ERROR >0 rollback tran--ROLLBACK TRANSACTION Else commit tran--COMMIT TRANSACTION goto Tranrollback-Go to destination Select*from student Select*f ROM score begin tran Delete from score where sno=107 delete from student where sno=109 if @ @ERROR >0 begin rollback Tran End ELSE begin commit Tran end
Database views, triggers, and transactions