· 5 years ago · Jun 08, 2020, 06:46 AM
1USE [CNPM_ThayThu_CHUNGKHOAN_1]
2GO
3/****** Object: Trigger [dbo].[after_lenhdat_insert] Script Date: 08-Jun-20 1:29:02 PM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8-- Batch submitted through debugger: SQLQuery4.sql|7|0|C:\Users\CrDo\AppData\Local\Temp\~vs4554.sql
9ALTER TRIGGER [dbo].[after_lenhdat_insert] ON [dbo].[LENHDAT] AFTER INSERT
10AS
11BEGIN
12 DECLARE @count1 int, -- top 2 lệnh M
13 @count2 int -- top 2 lệnh B
14 DECLARE @macp VARCHAR(7), @giadat FLOAT, @sl INT, @loaiGD CHAR(1)
15
16 SET @macp = (SELECT Inserted.MACP FROM Inserted)
17 SET @giadat = (SELECT Inserted.GIADAT FROM Inserted)
18 SET @sl = (SELECT Inserted.SOLUONG FROM Inserted)
19 SET @loaiGD = (SELECT Inserted.LOAIGD FROM Inserted)
20
21 IF NOT EXISTS (SELECT MACP FROM dbo.BANGTRUCTUYEN WHERE MACP = @macp)
22 BEGIN
23 INSERT INTO dbo.BANGTRUCTUYEN
24 ( MACP ,
25 GM2 ,
26 KM2 ,
27 GM1 ,
28 KM1 ,
29 GK ,
30 KK ,
31 GB1 ,
32 KB1 ,
33 GB2 ,
34 KB2
35 )
36 VALUES ( @macp , -- MACP - varchar(7)
37 0.0 , -- GM2 - float
38 0 , -- KM2 - int
39 0.0 , -- GM1 - float
40 0 , -- KM1 - int
41 0.0 , -- GK - float
42 0 , -- KK - int
43 0.0 , -- GB1 - float
44 0 , -- KB1 - int
45 0.0 , -- GB2 - float
46 0 -- KB2 - int
47 )
48 END
49
50 CREATE TABLE #topM ( gia FLOAT, soluong int);
51 CREATE TABLE #topB ( gia FLOAT, soluong int);
52 DECLARE @gm2 FLOAT = (SELECT GM2 FROM dbo.BANGTRUCTUYEN WHERE MACP = @macp)
53 DECLARE @gb2 FLOAT = (SELECT GB2 FROM dbo.BANGTRUCTUYEN WHERE MACP = @macp)
54
55 IF(@loaiGD = 'M')
56 BEGIN
57 IF( @gm2 > 0 AND @giadat >= @gm2 )
58 BEGIN
59 -- select top 2 lệnh mua
60 INSERT INTO #topM SELECT top 2 GIADAT, sum(SOLUONG) FROM LENHDAT
61 WHERE MACP = @macp AND LOAIGD = 'M' AND SOLUONG != 0 AND cast(NGAYDAT as Date) = cast(getdate() as Date)
62 GROUP BY GIADAT
63 ORDER BY GIADAT DESC
64 SET @count1 = (SELECT COUNT(*) FROM #topM)
65
66 IF(@count1 = 1)
67 UPDATE BANGTRUCTUYEN
68 SET GM1 = (SELECT TOP 1 gia FROM #topM), KM1 = (SELECT TOP 1 soluong FROM #topM), GM2 = 0, KM2 = 0
69 WHERE MACP = @macp
70 ELSE IF(@count1 = 2)
71 UPDATE BANGTRUCTUYEN
72 SET GM1 = (SELECT TOP 1 gia FROM #topM), KM1 = (SELECT TOP 1 soluong FROM #topM),
73 GM2 = (SELECT TOP 1 gia FROM #topM ORDER BY gia ASC), KM2 = (SELECT TOP 1 soluong FROM #topM ORDER BY gia ASC)
74 WHERE MACP = @macp
75
76 -- select top 2 lệnh bán
77 INSERT INTO #topB SELECT top 2 GIADAT, sum(SOLUONG) FROM LENHDAT
78 WHERE MACP = @macp AND LOAIGD = 'B' AND SOLUONG != 0 AND cast(NGAYDAT as Date) = cast(getdate() as Date)
79 GROUP BY GIADAT
80 ORDER BY GIADAT DESC
81
82 SET @count2 = (SELECT COUNT(*) FROM #topB)
83 IF(@count2 = 1)
84 UPDATE BANGTRUCTUYEN
85 SET GB1 = (SELECT TOP 1 gia FROM #topB), KB1 = (SELECT TOP 1 soluong FROM #topB), GB2 = 0, KB2 = 0
86 WHERE MACP = @macp
87 ELSE IF(@count2 = 2)
88 UPDATE BANGTRUCTUYEN
89 SET GB1 = (SELECT TOP 1 gia FROM #topB), KB1 = (SELECT TOP 1 soluong FROM #topB),
90 GB2 = (SELECT TOP 1 gia FROM #topB ORDER BY gia ASC), KB2 = (SELECT TOP 1 soluong FROM #topB ORDER BY gia ASC)
91 WHERE MACP = @macp
92 END
93 END
94 ELSE IF(@loaiGD = 'B')
95 BEGIN
96 IF( @gb2 > 0 AND @giadat <= @gb2 )
97 BEGIN
98 -- select top 2 lệnh mua
99 INSERT INTO #topM SELECT top 2 GIADAT, sum(SOLUONG) FROM LENHDAT
100 WHERE MACP = @macp AND LOAIGD = 'M' AND SOLUONG != 0 AND cast(NGAYDAT as Date) = cast(getdate() as Date)
101 GROUP BY GIADAT
102 ORDER BY GIADAT DESC
103 SET @count1 = (SELECT COUNT(*) FROM #topM)
104
105 IF(@count1 = 1)
106 UPDATE BANGTRUCTUYEN
107 SET GM1 = (SELECT TOP 1 gia FROM #topM), KM1 = (SELECT TOP 1 soluong FROM #topM), GM2 = 0, KM2 = 0
108 WHERE MACP = @macp
109 ELSE IF(@count1 = 2)
110 UPDATE BANGTRUCTUYEN
111 SET GM1 = (SELECT TOP 1 gia FROM #topM), KM1 = (SELECT TOP 1 soluong FROM #topM),
112 GM2 = (SELECT TOP 1 gia FROM #topM ORDER BY gia ASC), KM2 = (SELECT TOP 1 soluong FROM #topM ORDER BY gia ASC)
113 WHERE MACP = @macp
114
115 -- select top 2 lệnh bán
116 INSERT INTO #topB SELECT top 2 GIADAT, sum(SOLUONG) FROM LENHDAT
117 WHERE MACP = @macp AND LOAIGD = 'B' AND SOLUONG != 0 AND cast(NGAYDAT as Date) = cast(getdate() as Date)
118 GROUP BY GIADAT
119 ORDER BY GIADAT DESC
120
121 SET @count2 = (SELECT COUNT(*) FROM #topB)
122 IF(@count2 = 1)
123 UPDATE BANGTRUCTUYEN
124 SET GB1 = (SELECT TOP 1 gia FROM #topB), KB1 = (SELECT TOP 1 soluong FROM #topB), GB2 = 0, KB2 = 0
125 WHERE MACP = @macp
126 ELSE IF(@count2 = 2)
127 UPDATE BANGTRUCTUYEN
128 SET GB1 = (SELECT TOP 1 gia FROM #topB), KB1 = (SELECT TOP 1 soluong FROM #topB),
129 GB2 = (SELECT TOP 1 gia FROM #topB ORDER BY gia ASC), KB2 = (SELECT TOP 1 soluong FROM #topB ORDER BY gia ASC)
130 WHERE MACP = @macp
131 END
132 END
133
134END
135
136EXEC dbo.sp_delete_BTT