触发器的使用

--建表
create table Reader
(
    ReaderNo nvarchar(10),
    ReaderName nvarchar (20),
    Age smallint
)

create table Book
(
    [BookNo] nvarchar (10),
    [BookName] nvarchar (20)not null,
    [InBase] smallint not null,
)

create table Borrow
(
    [BorrowNo] int identity(1,1),
    [ReaderNo] nvarchar (10),
    [BookNo] nvarchar (10),
    [BDate] datetime,
    [RDate] datetime,
)

insert Reader values('r0','张三',19)
insert Reader values('r1','李司',20)
insert Reader values('r2','王五',21)

insert Book values('b0','C语言',1)
insert Book values('b1','数据结构',1)
insert Book values('b2','数据库',1)

insert Borrow values('r0','b0','2008-7-1',null)
insert Borrow values('r0','b1','2008-6-1',null)
insert Borrow values('r1','b2','2008-5-30',null)

create table [User]
(
    [id] bigint identity(1,1) primary key,
    username nvarchar(20) not null unique,
    Submit bigint ,
    Accepted bigint,
    solved bigint
)

insert into [User](username) values('u001')
insert into [User](username) values('u002')
insert into [User](username) values('u003')

create table Question
(
    [id] bigint identity(1,1) primary key,
    Description ntext,
    SubmitCount bigint,
    AcceptedCount bigint   
)

insert into Question(Description) values('A+B')
insert into Question(Description) values('A-B')
insert into Question(Description) values('A*B')

create table Answer
(
    [id] bigint identity(1,1) primary key,
    Questionid bigint references Question(id),
    username nvarchar(20)references [user](username),
    status tinyint,
    Addtime datetime,
    Code ntext
)

go

--（1）编写触发器，实现年龄范围在15~100的约束。
create trigger triggerCheckAge on reader
for insert,update as
    declare @age int
    select @age=age from inserted
    if @age<15 or @age>100
    begin
        raiserror('年龄越界',16,1)
        rollback transaction
    end
go

--（2）在库标记InBase设计为不能取空值，编写触发器，实现在库标记默认为1的约束。
--sql server 2005 OK
create trigger triggerDefaultInbase on book
instead of insert as
    insert into book select bookNo,bookName,1 from inserted
go

--（3）编写触发器保证每月1号不能借书。
create trigger triggerCheckDate on borrow
for insert as
    if (select day(Bdate) from inserted)=1
    begin
        raiserror('每月1号不能借书',16,1)
        rollback transaction
    end
go

--（4）保证每个读者在借图书数量不超过3本。
create trigger triggerCheckNums on borrow
for insert as
    if (select count(*) from inserted,borrow
        where inserted.readerNo=borrow.readerNo
        and borrow.Rdate is null)>3
    begin
        raiserror('1人数量不能超过3本',16,1)
        rollback transaction
    end
go

--（5）通过触发器实现主健约束。
create trigger triggerPrimaryKey on book
for insert,update as
    if (select bookNo from inserted) is null
    begin
        raiserror('书号不能为空',16,1)
        rollback transaction
    end

    if (select count(*) from book,inserted
    where book.bookNo=inserted.bookNo)>1
    begin
        raiserror('书号不能重复',16,1)
        rollback transaction
    end
go

--（6）通过触发器实现外键约束。
create trigger triggerForeignKey on borrow
for insert,update as
    if (select count(*) from inserted,reader
        where inserted.readerNo=reader.readerNo)=0
    begin
        raiserror('违反外键约束',16,1)
        rollback transaction
    end
go

--（7）通过触发器实现级联删除功能。
create trigger triggerDeleteCascade on book
for delete as
    delete from borrow from deleted
    where deleted.bookNo=borrow.bookNo
go

--（8）通过触发器实现受限删除功能。
alter table book disable trigger triggerDeleteCascade
go
create trigger triggerDeleteRestrict on book
for delete as
    if (select count(*) from deleted,borrow
        where deleted.bookNo=borrow.bookNo)>0
    begin
        raiserror('图书在借中',16,1)
        rollback transaction
    end
go

--（9）编写触发器来保证用户名的唯一。
create trigger triggerCheckUser on [user]
for insert as
    if (select count(*) from [user],inserted where [user].username=inserted.username)>1
    begin
        raiserror('用户重名',16,1)
        rollback transaction
    end
go

--（10）编写触发器，当答案表插入记录时，更新用户的解题情况和问题的解题情况。
create trigger triggerInsertAnswer on Answer
for insert
as
    declare @cnt bigint,@status tinyint,@qid bigint
    select @qid=questionId,@status=status from inserted 

    if (@status=99)
        begin
            update Question set SubmitCount=SubmitCount+1,
                AcceptedCount=AcceptedCount+1 where id=@qid

            select @cnt=count(*) from inserted,answer
            where inserted.userName=answer.userName and inserted.questionid=answer.questionid and
                       answer.status=99

           
            if (@cnt>1)
                update [user] set submit=submit+1,accepted=accepted+1
                from inserted
                where [user].userName=inserted.userName
            else
                update [user] set submit=submit+1,accepted=accepted+1,solved=solved+1
                from inserted
                where [user].userName=inserted.userName
        end
    else
        begin
            update Question set SubmitCount=SubmitCount+1 where id=@qid
            update [user] set submit=submit+1 from inserted
            where [user].userName=inserted.userName    
        end
go

--触发器是个好东西，特别是更新操作涉及多张表时。以上是触发器的基本使用方法。