→ lbeeon: trigger exec sp?08/21 12:45
老實說 我不太懂 我想法是 數量錯誤 觸發trigger update A01變0 再觸發DBMail寄送通
知 不知道對不對
※ 編輯: hildaz (111.83.163.165), 08/21/2015 13:23:06
USE [AIS20150116124346]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[OrderEntryCheck]
ON [dbo].[POOrderEntry]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
declare @FBillNO varchar(30)
declare @POFBNO varchar(20)
declare @FInterID bigint
declare @FItemID bigint
declare @FMaterial varchar(200)
declare @MSG varchar(1000)
select @FItemID=FItemID,@FINterID=FInterID
,@FBillNo=FEntrySelfP0253 from inserted
select @FMaterial=FNumber
from t_ICItem
where FItemID=@FItemID
select @POFBNO=T1.FBillNO
from POOrder T1
where T1.FInterID=@FInterID
if exists(select 1
from POOrderEntry T1
inner join POorder T2 on T2.FInterID=T1.FInterID
inner join t_BosConfig T3
on T3.FBillNO=T1.FEntrySelfP0253
inner join t_BosConfigEntry3 T4 on T4.FID=T3.FID
inner join t_ICItem T5 on T5.FItemID=T1.FItemID
where T4.FItemID = T1.FItemID
and T5.FNumber=@FMaterial
and T3.FBillNo=@FBillNo
and T2.FCancellation=0
and T2.FStatus > 0
and T2.FInterID <> @FInterID
group by T3.FBillNo,T2.FBIllNO,T5.FNumber
having sum(T4.FQty6) < sum(T1.FQty)
)
begin
select @MSG='訂單號:'+ @FBillNo + ' 採購單號:' + @POFBNO
+ ' 物料:' + @FMaterial + ' 重複採購'
raiserror('Error !!!',18,18)<<<應該是這邊要做修正用DBMAIL發信
對吧
end
※ 編輯: hildaz (202.55.252.114), 08/21/2015 13:42:52
推 rockchangnew: 用trigger這樣做，會搞死你的資料庫吧。 08/21 18:11
→ rockchangnew: 我覺得這種驗證應該是寫成SP來做吧。 08/21 18:15
→ hildaz: 喔喔 大大有好建議嗎 08/21 18:53
→ rockchangnew: 就用store procedure啊，不建議用trigger。還有要注 08/21 21:40
→ rockchangnew: 意執行SP時的帳號權限，因為你有calldbmail的SP 08/21 21:40
推 yzfr6: 從伊莉問到這裡來 真不簡單 08/25 00:03