create trigger tt on [Borrow] instead of insert as begin if (select count(*) from [Borrow] where [BNO] in(select [BNO] from inserted))<=4 begin insert into [Borrow] select * from inserted end end
CREATE TRIGGER tri_Insert_Borrow ON Borrow FOR insert AS IF EXISTS(SELECT 1 FROM Borrow b,inserted i where i.bno=b.bno group by b.bno having count(1)>5) BEGIN RAISERROR('所借图书数目已超过5!',16,1) ROLLBACK RETURN END GO
-- 应该是判断RNO create trigger tt on [Borrow] instead of insert as begin if (select count(*) from [Borrow] where [RNO] in(select [RNO] from inserted))<=4 begin insert into [Borrow] select * from inserted end end
你要在Borrow中创建触发器,然后插入值以后再判断要不要插入? 这个... 逻辑不对啊。你应该在存储过程中判断,是不是需要插入表Borrow。如果你要建成触发器,那么就是先插入表Borrow,然后触发器判断是不是应该插入,不应该就删除。邮电脱裤子放屁的意思啊。触发器大致如下: CREATE TRIGGER borrow_number ON borrow FOR INSERT AS BEGIN IF (SELECT RNO FROM INSERTED)>=5 DELETE FROM borrow WHERE BNO = (SELECT BNO FROM INSERTED)
create trigger tt on [Borrow]
instead of insert
as
begin
if (select count(*) from [Borrow] where [BNO] in(select [BNO] from inserted))<=4
begin
insert into [Borrow] select * from inserted
end
end
ON Borrow
FOR insert
AS
IF EXISTS(SELECT 1 FROM Borrow b,inserted i where i.bno=b.bno group by b.bno having count(1)>5)
BEGIN
RAISERROR('所借图书数目已超过5!',16,1)
ROLLBACK
RETURN
END
GO
create trigger tt on [Borrow]
instead of insert
as
begin
if (select count(*) from [Borrow] where [RNO] in(select [RNO] from inserted))<=4
begin
insert into [Borrow] select * from inserted
end
end
这个... 逻辑不对啊。你应该在存储过程中判断,是不是需要插入表Borrow。如果你要建成触发器,那么就是先插入表Borrow,然后触发器判断是不是应该插入,不应该就删除。邮电脱裤子放屁的意思啊。触发器大致如下:
CREATE TRIGGER borrow_number
ON borrow
FOR INSERT
AS
BEGIN IF (SELECT RNO FROM INSERTED)>=5
DELETE FROM borrow
WHERE BNO = (SELECT BNO FROM INSERTED)
END
而且这里使用Rollback的话,前面存储过程中要有begin Transaction啊。没有的话就出错了。
go
create table [Borrow]([BNO] int,[RNO] int,[Bdate] datetime)
insert [Borrow]
select 1001,1,'2012-6-27' union all
select 1001,2,'2012-6-27' union all
select 1001,3,'2012-6-27' union all
select 1001,4,'2012-6-27'
goCREATE TRIGGER tri_Insert_Borrow
ON Borrow
FOR insert
AS
IF EXISTS(SELECT 1 FROM Borrow b,inserted i where i.bno=b.bno group by b.bno having count(1)>5)
BEGIN
RAISERROR('所借图书数目已超过5!',16,1)
ROLLBACK
RETURN
END
GOinsert borrow select 1001,5,getdate()
/**(1 行受影响)
**/
select * from borrow
/**
BNO RNO Bdate
----------- ----------- -----------------------
1001 1 2012-06-27 00:00:00.000
1001 2 2012-06-27 00:00:00.000
1001 3 2012-06-27 00:00:00.000
1001 4 2012-06-27 00:00:00.000
1001 5 2012-06-27 23:00:40.970(5 行受影响)
**/
insert borrow select 1001,6,getdate()
/**
消息 50000,级别 16,状态 1,过程 tri_Insert_Borrow,第 7 行
所借图书数目已超过5!
消息 3609,级别 16,状态 1,第 1 行
事务在触发器中结束。批处理已中止。
**/
insert borrow select 1002,1,getdate()
go
select * from borrow
/**
BNO RNO Bdate
----------- ----------- -----------------------
1001 1 2012-06-27 00:00:00.000
1001 2 2012-06-27 00:00:00.000
1001 3 2012-06-27 00:00:00.000
1001 4 2012-06-27 00:00:00.000
1001 5 2012-06-27 23:00:40.970
1002 1 2012-06-27 23:02:18.767(6 行受影响)
**/
select @@version
/**-----------------------------------------------------------------------------------
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 6.1 (Build 7601: Service Pack 1)
(1 行受影响)
**/