create trigger tr_IssueProduct on IssueProduct
for insert
as
begin
declare @invseriesnr varchar(13)
select @invseriesnr=invseriesnr from inserted
declare @prodnr varchar(30)
select @prodnr=prodnr from insertedupdate IssueProduct set qty=ceiling(qty)
where invseriesnr=@invseriesnr and prodnr=@prodnr and
end
我写了个触发器,但这个触发器是对所以的插入到这个表的小数都进位, 我想达到只让414-% (414是一个分类,百分号是变动的 )这个分类的小数进位,其它的不进位一样可以插入到这个表,应该如何写!
for insert
as
begin
declare @invseriesnr varchar(13)
select @invseriesnr=invseriesnr from inserted
declare @prodnr varchar(30)
select @prodnr=prodnr from insertedupdate IssueProduct set qty=ceiling(qty)
where invseriesnr=@invseriesnr and prodnr=@prodnr and
end
我写了个触发器,但这个触发器是对所以的插入到这个表的小数都进位, 我想达到只让414-% (414是一个分类,百分号是变动的 )这个分类的小数进位,其它的不进位一样可以插入到这个表,应该如何写!
for insert
as
begin
update t set qty=(case when type='414-%' then ceiling(qty) else qty end)
from IssueProduct t
where exists(select 1 from inserted where invseriesnr=t.invseriesnr and prodnr=t.prodnr)
end
for insert
as
beginupdate a
set qty=ceiling(qty)
FROM INSERTED AS i,IssueProduct AS a
WHERE i.prodnr=a.prodnr AND i.invseriesnr=a.invseriesnr AND a.分類 LIKE '414-%'
end
for insert
as
begin
declare @invseriesnr varchar(13)
select @invseriesnr=invseriesnr from inserted
declare @prodnr varchar(30)
select @prodnr=prodnr from insertedupdate IssueProduct set qty=ceiling(qty)
where invseriesnr=@invseriesnr and prodnr like '414-%'
end
CREATE trigger tr_IssueProduct on IssueProduct
for insert
as
begin
declare @invseriesnr varchar(13)
select @invseriesnr=invseriesnr from inserted
declare @prodnr varchar(30)
select @prodnr=prodnr from insertedupdate IssueProduct set qty=(case when prodnr like '414-%' then ceiling(qty) else qty end)
where invseriesnr=@invseriesnr and prodnr=@prodnr
end
改成这样就可以了。