创建一个简单的Oracle SQl触发器

Val 发表于 Dev

值

我正在尝试为假数据库创建触发器。假设有一个名为BANK-BRANCH的派生属性“ total_loan”，用于维护每个分支机构的贷款总额。我正在尝试设计一个触发器来维护此派生属性。因此，我的触发器似乎停止了如下所示的load.sql脚本：

CREATE TABLE a2_bankbranch (
name           VARCHAR(200)  NOT NULL,
branch_num     VARCHAR(200)  NOT NULL,
address        VARCHAR(200)  NOT NULL,
routing_code   VARCHAR(200)  NOT NULL,
total_loan     NUMBER(38),
FOREIGN KEY(routing_code) REFERENCES a2_bank(routingcode),
PRIMARY KEY(branch_num, routing_code)
);
@trig.sql

INSERT INTO a2_bankbranch VALUES
( 'ASB', '5', '3 gladstone rd', '123456', '');
INSERT INTO a2_bankbranch VALUES
( 'ASB', '4', '28 stevee rd', '123456', '');

CREATE TABLE a2_loan (
loan_num       CHAR(10)  PRIMARY KEY,
type           VARCHAR(200)  NOT NULL,
amount         NUMBER(38)  NOT NULL,
contract_date  DATE          NOT NULL,
branch_num     VARCHAR(200) NOT NULL,
routing_code   VARCHAR(200) NOT NULL,
FOREIGN KEY(routing_code) REFERENCES a2_bank(routing_code),
Foreign KEY(branch_num) REFERENCES a2_bankbranch(branch_num)
);

INSERT INTO a2_loan VALUES
( '323', 'Mortgage', '2000000', TO_DATE('11-03-1994', 'DD-MM-YYYY'), '5', '123456');
INSERT INTO a2_loan VALUES
( '33', 'Car', '2000', TO_DATE('12-08-1994', 'DD-MM-YYYY'), '5', '123456' );
INSERT INTO a2_loan VALUES
( '3243', 'Pesonal', '875', TO_DATE('14-06-1994', 'DD-MM-YYYY'), '5', '123456' );
INSERT INTO a2_loan VALUES
( '6', 'Mortgage', '400500', TO_DATE('11-06-1994', 'DD-MM-YYYY'), '5', '123456' );

我的触发器如下所示，初始插入不起作用，并且我不确定要在WHERE和SET行中放置哪些内容以进行更新和删除Trig.sql如下所示

-- Create a trigger that will update the total loan amount
--that each bank brach may have

CREATE OR REPLACE TRIGGER ttl
AFTER INSERT OR UPDATE OR DELETE OF total_loan ON a2-bankbranch
FOR EACH ROW
BEGIN
IF INSERTING THEN
UPDATE a2_loan
SET total_loan = total_loan + :NEW.amount
WHERE branch_num = :NEW.bank_num;
ELSIF UPDATING THEN
UPDATE a2_loan
SET total_loan = total_loan + amount
WHERE
ELSE --deleting
UPDATE a2_loan
WHERE
END;

任何帮助将不胜感激

贾斯汀洞穴

首先，标准警告。在现实世界中，您永远不会使用触发器来执行此类操作。在现实世界中，您可能首先不应该存储非正规化的总计。如果这样做，则需要使用实例化视图进行维护。

话虽如此，我希望你想要类似的东西

CREATE OR REPLACE TRIGGER your_trigger_name
  AFTER INSERT, UPDATE, DELETE ON a2_loan
  FOR EACH ROW
BEGIN
  IF INSERTING 
  THEN
    UPDATE a2_bankbranch
       SET total_loan = nvl(total_loan,0) + :new.amount
     WHERE routing_code = :new.routing_code
       AND branch_num   = :new.branch_num;
  END IF;

  IF UPDATING
  THEN
    UPDATE a2_bankbranch
       SET total_loan = nvl(total_loan,0) - :old.amount
     WHERE routing_code = :old.routing_code
       AND branch_num   = :old.branch_num;

    UPDATE a2_bankbranch
       SET total_loan = nvl(total_loan,0) + :new.amount
     WHERE routing_code = :new.routing_code
       AND branch_num   = :new.branch_num;
  END IF;

  IF DELETING
  THEN
    UPDATE a2_bankbranch
       SET total_loan = nvl(total_loan,0) - :old.amount
     WHERE routing_code = :old.routing_code
       AND branch_num   = :old.branch_num;
  END IF;
END;