如何在Oracle SQL上实现此触发器? [英] How to implement this trigger on Oracle SQL?
问题描述
我在学习的SQL书中找到了一个练习，但尚未解决，我无法解决.
I have found an exercise in the SQL book I study, which is not solved and I can not solve it.
目标是实现一个避免重叠合同的触发器.如果具有当前合同的客户签订了新合同，则前一个合同的结束日期将是新合同开始日期的前一天.
The goal is to implement a trigger that avoids overlapping contracts. If a customer with a current contract signs a new one, the end date of the previous one will be one day before the new start date.
给出的表是:
CREATE TABLE CLIENTS (
clientId VARCHAR2(15),
DNI VARCHAR2(9),
name VARCHAR2(100) NOT NULL,
surname VARCHAR2(100) NOT NULL,
sec_surname VARCHAR2(100),
eMail VARCHAR2(100) NOT NULL,
phoneN NUMBER(12),
birthdate DATE,
CONSTRAINT PK_CLIENTS PRIMARY KEY (clientId),
CONSTRAINT UK1_CLIENTS UNIQUE (DNI),
CONSTRAINT UK2_CLIENTS UNIQUE (eMail),
CONSTRAINT UK3_CLIENTS UNIQUE (phoneN),
);
CREATE TABLE contracts(
contractId VARCHAR2(10),
clientId VARCHAR2(15),
startdate DATE NOT NULL,
enddate DATE,
contract_type VARCHAR2(50),
address VARCHAR2(100) NOT NULL,
town VARCHAR2(100) NOT NULL,
ZIPcode VARCHAR2(8) NOT NULL,
country VARCHAR2(100) NOT NULL,
CONSTRAINT PK_contracts PRIMARY KEY (contractId),
CONSTRAINT FK_contracts1 FOREIGN KEY (clientId) REFERENCES CLIENTS
);
有什么建议吗?
推荐答案
我同意所发布的评论，因为它有助于详细了解先前尝试中失败的原因，并且我也建议您不要使用TRIGGER完全是为了这种事情.
但是，由于这是一项学习练习，因此以下示例可能是一个起点.
I agree with the posted comments that it helps to have some specifics about what is failing in the prior attempts, and I would also recommend not using a TRIGGER at all for this kind of thing.
But as this is for a study exercise, here are some examples that might be a starting place.
在这些示例中，我已经修改了您的表以禁止NULL PRIMARY KEY.
I've modified your tables to disallow NULL PRIMARY KEYs in these examples.
开始创建表:
CREATE TABLE CLIENTS (
CLIENTID VARCHAR2(15) NOT NULL,
DNI VARCHAR2(9),
NAME VARCHAR2(100) NOT NULL,
SURNAME VARCHAR2(100) NOT NULL,
SEC_SURNAME VARCHAR2(100),
EMAIL VARCHAR2(100) NOT NULL,
PHONEN NUMBER(12),
BIRTHDATE DATE,
CONSTRAINT PK_CLIENTS PRIMARY KEY (CLIENTID),
CONSTRAINT UK1_CLIENTS UNIQUE (DNI),
CONSTRAINT UK2_CLIENTS UNIQUE (EMAIL),
CONSTRAINT UK3_CLIENTS UNIQUE (PHONEN)
);
CREATE TABLE CONTRACTS (
CONTRACTID VARCHAR2(10) NOT NULL,
CLIENTID VARCHAR2(15) NOT NULL,
STARTDATE DATE NOT NULL,
ENDDATE DATE,
CONTRACT_TYPE VARCHAR2(50),
ADDRESS VARCHAR2(100) NOT NULL,
TOWN VARCHAR2(100) NOT NULL,
ZIPCODE VARCHAR2(8) NOT NULL,
COUNTRY VARCHAR2(100) NOT NULL,
CONSTRAINT PK_CONTRACTS PRIMARY KEY (CONTRACTID),
CONSTRAINT FK_CONTRACTS1 FOREIGN KEY (CLIENTID) REFERENCES CLIENTS
);
然后，创建第一个CLIENT:
INSERT INTO CLIENTS VALUES (1,NULL,'Frodo','Baggins',NULL,'the.real.frodo@adventure.com',NULL,NULL);
INSERT INTO CLIENTS VALUES (2,NULL,'Chewbacca','UNKNOWN',NULL,'chewio.@kashyyyk.org',NULL,NULL);
COMMIT;
然后创建一个TRIGGER.在第一个示例中，TRIGGER是AFTER STATEMENT类型.
这很简单，但是效率很低，因为它会在每个INSERT语句之后评估每个 CLIENT.
对于大型数据集，或者面对多个TRIGGER，这可能是一个问题.
此TRIGGER将检查先前的合同，并将其ENDDATE设置为新合同之前的一天(如果为空)或在新合同开始之后.
Then create a TRIGGER. In this first example, the TRIGGER is an AFTER STATEMENT type.
It is simple but inefficient since it evaluates every CLIENT after each INSERT statement.
Against a large data set, or in the face of multiple TRIGGERs, this could be a problem.
This TRIGGER will check for the prior contract and will set its ENDDATE to one day before the new contract, if it is null or after the start of the new contract.
CREATE OR REPLACE TRIGGER CONTRACT_ENDDATE_ADJUSTER
AFTER INSERT ON CONTRACTS
BEGIN
MERGE INTO CONTRACTS
USING (
SELECT CONTRACTID,
CANDIDATE_ENDDATE AS ENDDATE
FROM
(SELECT CONTRACTS.CONTRACTID,
(TRUNC(LEAD(STARTDATE) OVER (PARTITION BY CLIENTID ORDER BY STARTDATE ASC) - 1)) AS CANDIDATE_ENDDATE,
DENSE_RANK() OVER (PARTITION BY CLIENTID ORDER BY STARTDATE DESC) AS CONTRACT_ORDER
FROM CONTRACTS)
WHERE CONTRACT_ORDER = 2) CANDIDATE_CONTRACT
ON (CONTRACTS.CONTRACTID = CANDIDATE_CONTRACT.CONTRACTID)
WHEN MATCHED THEN UPDATE SET CONTRACTS.ENDDATE = CANDIDATE_CONTRACT.ENDDATE
WHERE CONTRACTS.ENDDATE IS NULL OR CONTRACTS.ENDDATE > CANDIDATE_CONTRACT.ENDDATE;
END;
/
然后测试一下.
添加初始合同.预计不会更改结束日期，因为这是第一个. Frodo的合同此处已设定完结日期.
Then, test it out.
Add the initial contracts. No enddate changes expected, as these are the first. Frodo's contract here has an end-date already set.
INSERT INTO CONTRACTS VALUES('Break-Ring',1,TO_DATE('19560511','YYYYMMDD'), TO_DATE('19851014','YYYYMMDD'), NULL, 'No 1', 'Doom Mountain', 'MORD', 'Middle-Earth');
INSERT INTO CONTRACTS VALUES('SaveGalaxy',2,TO_DATE('19770615','YYYYMMDD'), NULL, NULL, 'No 75', 'Rwookrrorro', 'RWKR', 'Kashyyyk');
SELECT CONTRACTID, CLIENTID, STARTDATE, ENDDATE FROM CONTRACTS ORDER BY CLIENTID ASC, STARTDATE ASC;
CONTRACTID CLIENTID STARTDATE ENDDATE
Break-Ring 1 11-MAY-56 14-OCT-85
SaveGalaxy 2 15-JUN-77
然后添加新合同.
Frodo的新合同在其现有合同到期之前开始，因此终止日期将被调整.
Chewie的初始合同没有ENDDATE，因此也会进行调整.
Then add new contracts.
Frodo's new contract starts before the end of his existing contract, so the enddate will be adjusted.
Chewie's initial contract had no ENDDATE, so it will be adjusted as well.
INSERT INTO CONTRACTS VALUES('GoBackHome',1,TO_DATE('19570219','YYYYMMDD'), NULL, NULL, 'No 13', 'Hobbiton', 'HBTN', 'Middle-Earth');
INSERT INTO CONTRACTS VALUES('DefendHoth',2,TO_DATE('19801115','YYYYMMDD'), NULL, NULL, 'Meteor Crater', 'Ice Ridge', 'METEO', 'Hoth');
SELECT CONTRACTID, CLIENTID, STARTDATE, ENDDATE FROM CONTRACTS ORDER BY CLIENTID ASC, STARTDATE ASC;
CONTRACTID CLIENTID STARTDATE ENDDATE
Break-Ring 1 11-MAY-56 18-FEB-57
GoBackHome 1 19-FEB-57
SaveGalaxy 2 15-JUN-77 14-NOV-80
DefendHoth 2 15-NOV-80
随着其他合同的签订，模式继续:
And as other contracts are signed, the pattern continues:
INSERT INTO CONTRACTS VALUES('GoWedding',2,TO_DATE('19830309','YYYYMMDD'), NULL, NULL, 'Main Hall', 'Grand Palace', 'ALLNC', 'Coruscant');
INSERT INTO CONTRACTS VALUES('Gardening',1,TO_DATE('19570503','YYYYMMDD'), NULL, NULL, 'No 13', 'Hobbiton', 'HBTN', 'Middle-Earth');
SELECT CONTRACTID, CLIENTID, STARTDATE, ENDDATE FROM CONTRACTS ORDER BY CLIENTID ASC, STARTDATE ASC;
CONTRACTID CLIENTID STARTDATE ENDDATE
Break-Ring 1 11-MAY-56 18-FEB-57
GoBackHome 1 19-FEB-57 02-MAY-57
Gardening 1 03-MAY-57
SaveGalaxy 2 15-JUN-77 14-NOV-80
DefendHoth 2 15-NOV-80 08-MAR-83
GoWedding 2 09-MAR-83
为稳定此查询的工作负载，可以改用COMPOUND TRIGGER.第二个示例获得与第一个示例相同的结果，但是仅询问已更改的CLIENT中的CONTRACT:
To stabilize the workload on this query, a COMPOUND TRIGGER may be used instead. This second example achieves the same result as the first, but only interrogates the CONTRACTs of CLIENTs that have changed:
首先，ROLLBACK;
然后，创建要由TRIGGER使用的类型:
First, ROLLBACK;
Then, create a type to be used by the TRIGGER:
CREATE OR REPLACE TYPE NUMBER_LIST IS TABLE OF NUMBER;
/
然后创建COMPOUND TRIGGER:
CREATE OR REPLACE TRIGGER CONTRACT_ENDDATE_ADJUSTER
FOR INSERT ON CONTRACTS
COMPOUND TRIGGER
V_CLIENTS NUMBER_LIST;
BEFORE STATEMENT
IS
BEGIN
V_CLIENTS:= NUMBER_LIST();
END BEFORE STATEMENT;
AFTER EACH ROW
IS
BEGIN
V_CLIENTS.EXTEND();
V_CLIENTS(V_CLIENTS.COUNT) := :NEW.CLIENTID;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
MERGE INTO CONTRACTS
USING (
SELECT CONTRACTID,
CANDIDATE_ENDDATE AS ENDDATE
FROM
(SELECT CONTRACTS.CONTRACTID,
(TRUNC(LEAD(STARTDATE) OVER (PARTITION BY CLIENTID ORDER BY STARTDATE ASC) - 1)) AS CANDIDATE_ENDDATE,
DENSE_RANK() OVER (PARTITION BY CLIENTID ORDER BY STARTDATE DESC) AS CONTRACT_ORDER
FROM CONTRACTS
WHERE CONTRACTS.CLIENTID IN (SELECT * FROM TABLE(V_CLIENTS)))
WHERE CONTRACT_ORDER = 2) CANDIDATE_CONTRACT
ON (CONTRACTS.CONTRACTID = CANDIDATE_CONTRACT.CONTRACTID)
WHEN MATCHED THEN UPDATE SET CONTRACTS.ENDDATE = CANDIDATE_CONTRACT.ENDDATE
WHERE CONTRACTS.ENDDATE IS NULL OR CONTRACTS.ENDDATE > CANDIDATE_CONTRACT.ENDDATE;
END AFTER STATEMENT;
END CONTRACT_ENDDATE_ADJUSTER;
/
重复上述插入后，结果相同:
After repeating the above inserts, the result is the same:
CONTRACTID CLIENTID STARTDATE ENDDATE
Break-Ring 1 11-MAY-56 18-FEB-57
GoBackHome 1 19-FEB-57 02-MAY-57
Gardening 1 03-MAY-57
SaveGalaxy 2 15-JUN-77 14-NOV-80
DefendHoth 2 15-NOV-80 08-MAR-83
GoWedding 2 09-MAR-83
这篇关于如何在Oracle SQL上实现此触发器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
