[SQL ] MS SQL 2008R2 DBmail
資料庫名稱：MSSQL
資料庫版本：2008R2
內容/問題描述：
想請教 最近發現 SQL 2008R2 有所謂DBMAIL 通知的功能
小弟資質魯鈍 看了一堆網頁 還是不太懂網路上的說明>"<!!
想請教 假設 有個資料庫 A
欄位A01預設是1
當資料異常時 A01會變成0
而觸發DBMAIL 通知管理者
這有辦法做到嗎????
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 202.55.252.114
※ 文章網址: https://www.ptt.cc/bbs/Database/M.1440128179.A.3F6.html
→
08/21 12:45, , 1F
08/21 12:45, 1F
老實說 我不太懂 我想法是 數量錯誤 觸發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
推
08/21 18:11, , 2F
08/21 18:11, 2F
→
08/21 18:15, , 3F
08/21 18:15, 3F
→
08/21 18:53, , 4F
08/21 18:53, 4F
→
08/21 21:40, , 5F
08/21 21:40, 5F
→
08/21 21:40, , 6F
08/21 21:40, 6F
推
08/25 00:03, , 7F
08/25 00:03, 7F