SQL知识整理一：触发器、存储过程、表变量、临时表

create trigger tr_name 
on table/view 
{for | after | instead of } [update][,][insert][,][delete] 
[with encryption] 
as {batch | if update (col_name) [{and|or} update (col_name)] } 

if exists(select name from sysobjects where xtype='tr' and name='tri_updateStudent')
begin
    drop trigger tri_UpdateStudent
end
go
create trigger tri_UpdateStudent
    on dbo.student
    for update
as
    if update(Sage)
    begin
       update student set sage=s.sage+d.sage from student s,deleted d where s.studentid=d.studentid
    end
go

if exists(select * from sysobjects where xtype='p' and name='proc_Student')

begin 

    drop proc proc_student

end

go

create proc proc_Student

    @name varchar(255),

    @age varchar(255)

as

    begin tran

    select * from student where  sname=@name and sage=@age

    if @@ERROR<>0

       begin

           rollback tran

           insert into student(studentid,sname,sage) values (1,@name,@age)

           return 0

       end

    else

        begin

       commit tran

       select * from student

       end

go

exec proc_student '程兴亮',1;

DECLARE @News table 

　　( 

　　News_id int NOT NULL, 

　　NewsTitle varchar(100), 

　　NewsContent varchar(2000), 

　　NewsDateTime datetime 

　　) 

　　INSERT INTO @News (News_id, NewsTitle, NewsContent, NewsDateTime) 

　　VALUES (1,'BlueGreen', 'Austen', 200801, GETDATE()) 

　　SELECT News_id, NewsTitle, NewsContent, NewsDateTime FROM @News

CREATE TABLE dbo.#News 
　　( 
　　News_id int NOT NULL, 
　　NewsTitle varchar(100), 
　　NewsContent varchar(2000), 
　　NewsDateTime datetime 
　　) 
　　INSERT INTO dbo.#News (News_id, NewsTitle, NewsContent, NewsDateTime) 
　　VALUES (1,'BlueGreen', 'Austen', 200801, GETDATE()) 
　　SELECT News_id, NewsTitle, NewsContent, NewsDateTime FROM dbo.#News 
　　DROP TABLE dbo.[#News]