22,300
社区成员




CREATE TABLE tPPTN(STCD int,
TM smalldatetime,
DRP int,
INTV int,
DYP int
)
INSERT INTO tPPTN(STCD, TM, DRP, INTV)
SELECT 31128350, '2014-05-11 04:00:00', 1, 2 UNION ALL
SELECT 31128350, '2014-05-11 02:00:00', 2, 2 UNION ALL
SELECT 31128350, '2014-05-11 00:00:00', 3, 2 UNION ALL
SELECT 31128350, '2014-05-10 22:00:00', 6, 2 UNION ALL
SELECT 31128350, '2014-05-10 20:00:00', 0, 2 UNION ALL
SELECT 31128350, '2014-05-10 18:00:00', 2, 2 UNION ALL
SELECT 31128350, '2014-05-10 16:00:00', 4, 2 UNION ALL
SELECT 31128350, '2014-05-10 14:00:00', 0, 2 UNION ALL
SELECT 31128350, '2014-05-10 12:00:00', 1, 2 UNION ALL
SELECT 31128355, '2014-05-10 22:00:00', 3, 2 UNION ALL
SELECT 31128355, '2014-05-10 18:00:00', 0, 2 UNION ALL
SELECT 31128355, '2014-05-10 16:00:00', 1, 2
go
SELECT * FROM tPPTN
GO
CREATE TRIGGER t_REPLACE ON tPPTN
AFTER INSERT
AS
BEGIN
IF EXISTS(SELECT 1 FROM inserted WHERE DATEPART(hh, TM)=6)
BEGIN
UPDATE tPPTN
SET DYP=t.DYP
FROM tPPTN a INNER JOIN
(
SELECT i.STCD, i.TM, SUM(p.DRP) AS DYP
FROM tPPTN p INNER JOIN inserted i
ON p.STCD=i.STCD AND p.INTV=2 AND DATEPART(hh, i.TM)=6
WHERE p.TM>=DATEADD(day,-1,i.TM) AND p.TM<i.TM
GROUP BY i.STCD, i.TM
) t ON a.STCD=t.STCD AND a.TM=t.TM
END
END
go
INSERT INTO tPPTN(STCD, TM, DRP, INTV)
SELECT 31128355, '2014-05-11 08:00:00', 2, 2 UNION ALL
SELECT 31128355, '2014-05-11 06:00:00', 1, 2 UNION ALL
SELECT 31128350, '2014-05-11 06:00:00', 0, 2
GO
SELECT * FROM tPPTN ORDER BY STCD, TM
GO
DROP TRIGGER t_REPLACE
DROP TABLE tPPTN
CREATE TABLE tPPTN(STCD int,
TM smalldatetime,
DRP int,
INTV int,
DYP int
)
INSERT INTO tPPTN(STCD, TM, DRP, INTV)
SELECT 31128350, '2014-05-11 04:00:00', 1, 2 UNION ALL
SELECT 31128350, '2014-05-11 02:00:00', 2, 2 UNION ALL
SELECT 31128350, '2014-05-11 00:00:00', 3, 2 UNION ALL
SELECT 31128350, '2014-05-10 22:00:00', 6, 2 UNION ALL
SELECT 31128350, '2014-05-10 20:00:00', 0, 2 UNION ALL
SELECT 31128350, '2014-05-10 18:00:00', 2, 2 UNION ALL
SELECT 31128350, '2014-05-10 16:00:00', 4, 2 UNION ALL
SELECT 31128350, '2014-05-10 14:00:00', 0, 2 UNION ALL
SELECT 31128350, '2014-05-10 12:00:00', 1, 2 UNION ALL
SELECT 31128355, '2014-05-10 22:00:00', 3, 2 UNION ALL
SELECT 31128355, '2014-05-10 18:00:00', 0, 2 UNION ALL
SELECT 31128355, '2014-05-10 16:00:00', 1, 2
go
SELECT * FROM tPPTN
GO
CREATE TRIGGER t_REPLACE ON tPPTN
AFTER INSERT
AS
BEGIN
IF EXISTS(SELECT 1 FROM inserted WHERE DATEPART(hh, TM)=6)
BEGIN
UPDATE tPPTN
SET DYP=(SELECT SUM(DRP) FROM tPPTN p
WHERE p.STCD=a.STCD AND p.INTV=2
AND p.TM>=DATEADD(day,-1,a.TM) AND p.TM<a.TM
)
FROM tPPTN a
INNER JOIN inserted i
ON a.STCD=i.STCD AND a.TM=i.TM AND DATEPART(hh, i.TM)=6
END
END
go
INSERT INTO tPPTN(STCD, TM, DRP, INTV)
SELECT 31128355, '2014-05-11 08:00:00', 2, 2 UNION ALL
SELECT 31128355, '2014-05-11 06:00:00', 1, 2 UNION ALL
SELECT 31128350, '2014-05-11 06:00:00', 0, 2
GO
SELECT * FROM tPPTN
GO
DROP TRIGGER t_REPLACE
DROP TABLE tPPTN
CREATE TRIGGER t_REPLACE ON tPPTN
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS(SELECT 1 FROM inserted WHERE DATEPART(hh, TM)=6)
BEGIN
INSERT INTO tPPTN(STCD, TM, DRP, INTV, DYP)
SELECT i.STCD, i.TM, i.DRP, i.INTV, SUM(p.DRP)
FROM tPPTN p
INNER JOIN inserted i
ON p.STCD=i.STCD AND p.INTV=2 AND DATEPART(hh, i.TM)=6
WHERE p.TM>=DATEADD(day,-1,i.TM) AND p.TM<i.TM
GROUP BY i.STCD, i.TM, i.DRP, I.INTV
END
END
CREATE TABLE tPPTN(STCD int,
TM smalldatetime,
DRP int,
INTV int,
DYP int
)
INSERT INTO tPPTN(STCD, TM, DRP, INTV)
SELECT 31128350, '2014-05-11 04:00:00', 1, 2 UNION ALL
SELECT 31128350, '2014-05-11 02:00:00', 2, 2 UNION ALL
SELECT 31128350, '2014-05-11 00:00:00', 3, 2 UNION ALL
SELECT 31128350, '2014-05-10 22:00:00', 6, 2 UNION ALL
SELECT 31128350, '2014-05-10 20:00:00', 0, 2 UNION ALL
SELECT 31128350, '2014-05-10 18:00:00', 2, 2 UNION ALL
SELECT 31128350, '2014-05-10 16:00:00', 4, 2 UNION ALL
SELECT 31128350, '2014-05-10 14:00:00', 0, 2 UNION ALL
SELECT 31128350, '2014-05-10 12:00:00', 1, 2 UNION ALL
SELECT 31128355, '2014-05-10 22:00:00', 3, 2 UNION ALL
SELECT 31128355, '2014-05-10 18:00:00', 0, 2 UNION ALL
SELECT 31128355, '2014-05-10 16:00:00', 1, 2
go
SELECT * FROM tPPTN
GO
CREATE TRIGGER t_REPLACE ON tPPTN
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS(SELECT 1 FROM inserted WHERE DATEPART(hh, TM)=6)
BEGIN
INSERT INTO tPPTN(STCD, TM, DRP, INTV, DYP)
SELECT i.STCD, i.TM, i.DRP, i.INTV, SUM(p.DRP)
FROM tPPTN p
INNER JOIN (SELECT * FROM inserted WHERE DATEPART(hh, TM)=6) i
ON p.STCD=i.STCD AND p.INTV=2
WHERE p.TM>=DATEADD(day,-1,i.TM) AND p.TM<i.TM
GROUP BY i.STCD, i.TM, i.DRP, I.INTV
END
END
go
INSERT INTO tPPTN(STCD, TM, DRP, INTV)
SELECT 31128350, '2014-05-11 06:00:00', 2, 2 UNION ALL
SELECT 31128355, '2014-05-11 06:00:00', 0, 2
GO
SELECT * FROM tPPTN
GO
DROP TRIGGER t_REPLACE
DROP TABLE tPPTN
CREATE TABLE tPPTN(STCD int,
TM smalldatetime,
DRP int,
INTV int,
DYP int
)
INSERT INTO tPPTN(STCD, TM, DRP, INTV)
SELECT 31128350, '2014-05-11 04:00:00', 1, 2 UNION ALL
SELECT 31128350, '2014-05-11 02:00:00', 2, 2 UNION ALL
SELECT 31128350, '2014-05-11 00:00:00', 3, 2 UNION ALL
SELECT 31128350, '2014-05-10 22:00:00', 6, 2 UNION ALL
SELECT 31128350, '2014-05-10 20:00:00', 0, 2 UNION ALL
SELECT 31128350, '2014-05-10 18:00:00', 2, 2 UNION ALL
SELECT 31128350, '2014-05-10 16:00:00', 4, 2 UNION ALL
SELECT 31128350, '2014-05-10 14:00:00', 0, 2 UNION ALL
SELECT 31128350, '2014-05-10 12:00:00', 1, 2 UNION ALL
SELECT 31128355, '2014-05-10 12:00:00', 3, 2 UNION ALL
SELECT 31128355, '2014-05-10 12:00:00', 2, 2
go
SELECT * FROM tPPTN
GO
CREATE TRIGGER t_REPLACE ON tPPTN
INSTEAD OF INSERT
AS
BEGIN
IF (SELECT DATEPART(hh, TM) FROM inserted)=6
BEGIN
DECLARE @dyp int
SELECT @dyp=SUM(p.DRP)
FROM tPPTN p INNER JOIN inserted i ON p.STCD=i.STCD
WHERE p.TM>=DATEADD(day,-1,i.TM) AND p.TM<i.TM AND p.INTV=2
INSERT INTO tPPTN(STCD, TM, DRP, INTV, DYP)
SELECT i.STCD, i.TM, i.DRP, I.INTV, @dyp
FROM inserted i
END
END
go
INSERT INTO tPPTN(STCD, TM, DRP, INTV)
SELECT 31128350, '2014-05-11 06:00:00', 2, 2
GO
SELECT * FROM tPPTN
GO
DROP TRIGGER t_REPLACE
DROP TABLE tPPTN