触发器，根据具体业务写弹窗提示

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
USE [XXXXX]
GO
/****** Object:  Trigger [dbo].[trg_Biz_Samp_19]    Script Date: 2016/4/22 9:58:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--------先接收任务再样品领取,结果登记提示--------------
ALTER trigger [dbo].[trg_Biz_Samp_19] ON [dbo].[biz_Samp]
AFTER insert,UPDATE 
AS
BEGIN
   IF UPDATE(SampStatusId)
   BEGIN
      declare @batchid uniqueidentifier = newid();


 BEGIN TRAN
 
 INSERT INTO GNotification(GContent, GNavigateUri, BizId, CreateBatchId)
      SELECT '您有新的待办事项【结果登记】: ' , 'f103015' ,INSERTED.SampId, @batchid  
 FROM INSERTED 
 INNER JOIN DELETED ON DELETED.SampId = INSERTED.SampId 
 left join biz_DeptTask on biz_DeptTask.TaskId = INSERTED.TaskId
 


 WHERE 
 INSERTED.SampStatusId != DELETED.SampStatusId   
 and INSERTED.SampStatusId = 420
 and exists(select 1 from biz_DeptTask where TestStatusId >= 310  )
 --and INSERTED.TaskId = biz_DeptTask.TaskId)                            ----确保是先接收了任务状态


   INSERT INTO GNotificationReceiver(GNotiId, GReceiveUserId)
SELECT n.GNotiId, u.UserId FROM GNotification n
INNER JOIN biz_Samp s ON s.SampId = n.BizId
INNER JOIN biz_DeptTask dt on dt.TaskId = s.TaskId
INNER JOIN sys_User u ON u.DeptId = dt.DeptId AND (',' + s.TesterIds + ',') LIKE ('%,'+ CONVERT(varchar(50), u.UserSN) +',%')  


----- 一个样品对应两个部门的两个人A,B，所以一个sampid 对应了两个主检人，比如：A接收，B不接收，A领取样品，应通知A，因为>=310,此时此两主检人链接的都是A的部门任务
        -----我们要通知的那个主检人是A，：该部门任务的主检人部门=u1.deptid，筛选掉B。         
----INNER JOIN后面的ON后的AND  等价于  WHERE 后面跟条件  。LEFT JOIN 则不然，ON后面不符合要求的空记录，只要左边有就会出来,有NULL。


WHERE n.CreateBatchId = @batchId AND dt.TestStatusId >= 310,
AND EXISTS(SELECT 1 FROM View_UserFucntion uf WHERE uf.UserId = u.UserId AND uf.FunctionId = CONVERT(INT, SUBSTRING(n.GNavigateUri, 2, 1000))) --用户必须是有功能权限的
GROUP BY n.GNotiId, u.UserId


 COMMIT TRAN
   END
END