用户输入数值的时候,如果输入的数值在数据库中有相同的数值,就会报错也就是说你需要的是当用户插入数据的时候触发这个触发器,所以insert肯定要加上的阿!CREATE TRIGGER Bom_Partnumber_Update ON [CQAdmin].[bom] INSTEAD OF insert,update AS declare @partnumber varchar(50) select @partnumber=i.partnumber from inserted as i IF @partnumber <> NULL BEGIN select bom.partnumber from bom, inserted as i where bom.partnumber=i.partnumber select @@rowcount IF @@rowcount<>0 BEGIN RAISERROR ('Transaction cannot be processed.\ ***** Partnumber ID overlap.', 10, 1) ROLLBACK TRANSACTION END else begin --这里插入数据 end END
这里你需要的是INSTEAD OF 触发器 因为如果该数据重复的话,你就不需要插入数据,而是给用户提示 不重复的时候才插入数据 如果用for insert,update的话,那肯定是先插入数据才作判断的!
经楼上提醒后 修改代码如下: CREATE TRIGGER Bom_Partnumber_Update ON [CQAdmin].[bom] INSTEAD OF insert,update AS declare @partnumber varchar(50) select @partnumber=i.partnumber from inserted as i IF @partnumber <> NULL BEGIN select bom.partnumber from bom, inserted as i where bom.partnumber=i.partnumber select @@rowcount IF @@rowcount<>0 BEGIN RAISERROR ('Transaction cannot be processed.\ ***** Partnumber ID overlap.', 10, 1) ROLLBACK TRANSACTION END else begin set partnumber=NULL end END 现在这样写以后 无论用户输入什么数值 都不能保存 也没有提示的窗口 是不是还有问题呀
--这里有问题 else begin insert into bom select * from inserted end
如果楼主真的要那样子做,将 select @@rowcount 注销就可以了CREATE TRIGGER Bom_Partnumber_Update ON [CQAdmin].[bom] FOR UPDATE AS declare @partnumber varchar(50) select @partnumber=i.partnumber from inserted as i IF @partnumber <> NULL BEGIN select bom.partnumber from bom, inserted as i where bom.partnumber=i.partnumber
IF @@rowcount<>0 BEGIN RAISERROR ('Transaction cannot be processed.\ ***** Partnumber ID overlap.', 10, 1) ROLLBACK TRANSACTION END END
呵呵,想想楼主的语句还有问题,这样吧CREATE TRIGGER Bom_Partnumber_Update ON [CQAdmin].[bom] FOR instead of insert,update AS select bom.partnumber from bom, inserted as i where bom.partnumber=i.partnumber
IF @@rowcount<>0 BEGIN RAISERROR ('Transaction cannot be processed.\ ***** Partnumber ID overlap.', 10, 1) ROLLBACK TRANSACTION END else insert bom select *from inserted END
现在还是不行报语法错误 在最后一个end那有错误
RAISERROR ('Transaction cannot be processed.\ ***** Partnumber ID overlap.', 10, 1)写成一行!
ON [CQAdmin].[bom]
INSTEAD OF insert,update
AS
declare @partnumber varchar(50)
select @partnumber=i.partnumber from inserted as i
IF @partnumber <> NULL
BEGIN
select bom.partnumber from bom, inserted as i where bom.partnumber=i.partnumber
select @@rowcount
IF @@rowcount<>0
BEGIN
RAISERROR ('Transaction cannot be processed.\
***** Partnumber ID overlap.', 10, 1)
ROLLBACK TRANSACTION
END
else
begin
--这里插入数据
end
END
因为如果该数据重复的话,你就不需要插入数据,而是给用户提示
不重复的时候才插入数据
如果用for insert,update的话,那肯定是先插入数据才作判断的!
修改代码如下:
CREATE TRIGGER Bom_Partnumber_Update
ON [CQAdmin].[bom]
INSTEAD OF insert,update
AS
declare @partnumber varchar(50)
select @partnumber=i.partnumber from inserted as i
IF @partnumber <> NULL
BEGIN
select bom.partnumber from bom, inserted as i where bom.partnumber=i.partnumber
select @@rowcount
IF @@rowcount<>0
BEGIN
RAISERROR ('Transaction cannot be processed.\
***** Partnumber ID overlap.', 10, 1)
ROLLBACK TRANSACTION
END
else
begin
set partnumber=NULL
end
END
现在这样写以后
无论用户输入什么数值
都不能保存
也没有提示的窗口
是不是还有问题呀
else
begin
insert into bom
select *
from inserted
end
select @@rowcount 注销就可以了CREATE TRIGGER Bom_Partnumber_Update
ON [CQAdmin].[bom]
FOR UPDATE
AS
declare @partnumber varchar(50)
select @partnumber=i.partnumber from inserted as i
IF @partnumber <> NULL
BEGIN
select bom.partnumber from bom, inserted as i where bom.partnumber=i.partnumber
IF @@rowcount<>0
BEGIN
RAISERROR ('Transaction cannot be processed.\
***** Partnumber ID overlap.', 10, 1)
ROLLBACK TRANSACTION
END
END
ON [CQAdmin].[bom]
FOR instead of insert,update
AS select bom.partnumber from bom, inserted as i where bom.partnumber=i.partnumber
IF @@rowcount<>0
BEGIN
RAISERROR ('Transaction cannot be processed.\
***** Partnumber ID overlap.', 10, 1)
ROLLBACK TRANSACTION
END
else
insert bom select *from inserted
END
在最后一个end那有错误
***** Partnumber ID overlap.', 10, 1)写成一行!