CODEWITHSUNDEEP
sqldatabaseplsqltriggers
magna_nz
magna_nz

Reputation: 1273

bad bind variable trigger for update and inserting

I keep getting a bad bind variable error. I have tried debugging it but this is still lingering over, not too sure why it's saying this here.

What I'm trying to do as if someone is inserted into the enrols database BEFORE 1993 then they get a 10% discount for that paper. Also, if updating the details and they were enrolled before start of 1993, then give them a 10% discount.

errors:
6/31     PLS-00049: bad bind variable 'OLD.PAPER_COST'
6/50     PLS-00049: bad bind variable 'OLD.PAPER_COST'
14/31    PLS-00049: bad bind variable 'NEW.PAPER_COST'
14/50    PLS-00049: bad bind variable 'NEW.PAPER_COST'

Shouldn't this be okay, that I'm not referencing paper_cost in my enrols relation but in my papers relation as I'm calling "update papers" prior to paper_cost?

CREATE OR REPLACE TRIGGER discount_160
BEFORE INSERT OR UPDATE ON enrols
FOR EACH ROW
BEGIN
    IF UPDATING THEN
      IF (date_enrolled < TO_DATE('01-Jan-1993', 'dd-mon-yyyy')) THEN
         IF (paper_code = 160) THEN
            UPDATE papers
            SET paper_cost = (:OLD.paper_cost - (:OLD.paper_cost * 0.1))
            WHERE papers.paper_code = enrols.paper_code;
       END IF;
    END IF;
    ELSE --inserting
      IF (date_enrolled < TO_DATE('01-Jan-1993', 'dd-mon-yyyy')) THEN
         IF (paper_code = 160) THEN
            UPDATE papers
            SET paper_cost = (:NEW.paper_cost - (:NEW.paper_cost * 0.1))
            WHERE papers.paper_code = enrols.paper_code;
         END IF;
      END IF;
   END IF;
END;
/

My enrols relation:

CREATE TABLE enrols
       (paper_code         INT     ,
       student_id         INT      REFERENCES student(student_id),
       date_enrolled      DATE,
       dept_id            INT,
       PRIMARY KEY(paper_code, student_id, dept_id),
       FOREIGN KEY (paper_code, dept_id) REFERENCES papers(paper_code, dept_id));

INSERT INTO enrols VALUES
   (160, 172384, TO_DATE('22-Mar-1994', 'dd-mon-yyyy'), 01);
INSERT INTO enrols VALUES
   (444, 849294, TO_DATE('14-Jul-1992', 'dd-mon-yyyy'), 04);
INSERT INTO enrols VALUES
   (160, 384583, TO_DATE('07-Aug-1995', 'dd-mon-yyyy'), 01);
INSERT INTO enrols VALUES
   (160, 999999, TO_DATE('18-Aug-1991', 'dd-mon-yyyy'), 01);

My papers relation:

CREATE TABLE papers
   (paper_code      INT,
   EFTS             INT     NOT NULL,
   dept_id          INT     REFERENCES departments(dept_id),
   paper_cost       INT,  
   PRIMARY KEY(paper_code, dept_id));


INSERT INTO papers VALUES
   (160, 0.18, 01, 800);

Any help to point me in the right direction would be great.

Upvotes: 0

Views: 1228

Answers (2)

TommCatt
TommCatt

Reputation: 5636

Many mistakes in the trigger.

BEGIN
    IF UPDATING THEN
(1)   IF ([:new.|:old.]date_enrolled < TO_DATE('01-Jan-1993', 'dd-mon-yyyy')) THEN
(1)      IF ([:new.|:old.]paper_code = 160) THEN
            UPDATE papers
            SET paper_cost = (:OLD.paper_cost - (:OLD.paper_cost * 0.1))
(2)         WHERE papers.paper_code = <strike>enrols</strike>[:new].paper_code;
       END IF;
    END IF;
    ELSE --inserting
(3)   IF ([:new.]date_enrolled < TO_DATE('01-Jan-1993', 'dd-mon-yyyy')) THEN
(3)      IF ([:new.]paper_code = 160) THEN
            UPDATE papers
            SET paper_cost = (:NEW.paper_cost - (:NEW.paper_cost * 0.1))
(2)         WHERE papers.paper_code = <strike>enrols</strike>[:new].paper_code;

(1): The trigger has two pseudo-rows available to it: NEW and OLD. When Updating, the OLD row contains the data as it currently exists in the table. The NEW row contains the row as it will look like after the Update completes. So you can't just call out date_enrolled. Do you mean :new.date_enrolled or :old.date_enrolled? The same thing for paper_code in two places.

(2): you are issuing an Update statement for table papers, yet you are referring to something called enrols. Where did that come from? You have not defined it. You and I know you mean the table enrols but the compiler doesn't know that. Besides, you don't need the table, you're already in the trigger for that table and you have the data you need at hand in one of the pseudo-rows. I think you want the one in :NEW but that's your decision. Also, as it appears that paper_cost is in both tables (not the best data modeling idea), you should probably change the :NEW.paper_cost also.

(3): in the Insert section of the trigger, the :OLD pseudo-row just has NULLs so it's a pretty safe bet you want to use :NEW here.

Not errors as such but just some technique: declare a local date variable called, oh, CutoffDate to place the result of the TO_DATE call. Then just refer to that variable instead of calling the function in two places. Also, a more direct way of reducing a value by 10% is SET paper_cost = :OLD.paper_cost * 0.9.

Also, as the same code is executed in both the inserting and updating sections, why not combine them?

So the new trigger would look something like this:

CREATE OR REPLACE TRIGGER discount_160
BEFORE INSERT OR UPDATE ON enrols
FOR EACH ROW
DECLARE
    CUTOFF  DATE := TO_DATE( '01-Jan-1993', 'dd-mon-yyyy' );
BEGIN
    IF :new.date_enrolled < CUTOFF AND
       :new.paper_code = 160 THEN
        :new.paper_cost := :old.paper_cost * 0.9;
        UPDATE papers
          SET paper_cost = :new.paper_cost
        WHERE paper_code = :new.paper_code;
    END IF;
END;

I haven't tried compiling it, let me know if I goofed up somewhere or if you have questions.

Upvotes: 1

Roman
Roman

Reputation: 35

you can try to refer field paper_cost as papers.paper_cost in your statements, not using :NEW or :OLD

Upvotes: 0

Related Questions