CODEWITHSUNDEEP
oracle-databaseplsql
Kyle Grage
Kyle Grage

Reputation: 765

Trigger to Insert New Row in Records Table

I have two tables.

The first table is Routes with schema (Train_Number, Route_Status, Time_Status, Route_TM, Departure, Destination) and a second table Records with schema (Train_Number, Time_Status, Route_TM, Departure, Destination).

I need a trigger such that when Route Status changes from 'En-Route' to 'Arrived', It logs the (Train_Number, Time_Status, Date, Departure, Destination) from Routes to Records.

Also in play is another table Configuration with schema (Name, Value). In particular, in that table, I have ('Enable_Logging', 'T')

Here is my attempt at this trigger:

CREATE OR REPLACE TRIGGER Train_Logging
AFTER UPDATE OF Route_Status ON Routes

FOR EACH ROW
WHEN (new.route_status = 'Arrived' AND old.route_status = 'En-Route')

DECLARE
logging_flag Configuration.value%type;

BEGIN

SELECT Configuration.value INTO logging_flag FROM Configuration WHERE Configuration = 'Enable_Logging';

IF (logging_flag = 'T') THEN INSERT INTO Records (Train_Number, Time_Status, Date, Departure, Destination) VALUES (SELECT Train_Number, Time_Status, Date, Departure, Destination FROM Routes);
END IF;
END Train_Logging;

My errors are "PL/SQL Statement Ignored" (at logging_flag Configuration.value%type;) and "ORA-00936 missing expression" (at BEGIN)

What is wrong with my declaration statement?

Upvotes: 0

Views: 895

Answers (1)

Alex Poole
Alex Poole

Reputation: 191580

Ignoring problems with your schema as stated - you seem to have a field called date which is not allowed, unless it's quoted, and you're checking a status field that doesn't existing the table description you showed, and you're referring to configuration.configuration in a select instead of configuration.name - with a bit of tweaking you get an error stack like:

8/122          PL/SQL: ORA-00936: missing expression
8/30           PL/SQL: SQL Statement ignored

And line 8 appears to be the logging_flag declaration; but actually the line numbers in the error refer to the PL/SQL block, not to the trigger definition that comes before that, so line 8 is really line 14 in your statement. If you split that line out so the insert goes onto a new line, and the values also goes onto a new line, the stack becomes:

10/9           PL/SQL: ORA-00936: missing expression
9/1            PL/SQL: SQL Statement ignored

So the insert is being ignored because there's something wrong with the values clause. If you did want to insert from a select you wouldn't use the values keyword at all; you'd do:

INSERT INTO Records (Train_Number, Time_Status, Train_Date,
  Departure, Destination)
SELECT Train_Number, Time_Status, Train_Date, Departure, Destination
FROM Routes;

(You can use a select inside a values clause, but only to get a single value, and then it would in another set of parentheses, and that isn't what you want here.)

But you don't want or need select from the table you're inserting into. Your select has no where clause so you'd be copying the entire routes table into the records table for every time any row was updated, which surely isn;t what you want. You only want to copy the particular row affected by the update. But you don't need a select for that, you have all the values in the new and old pseudo-rows:

INSERT INTO Records (Train_Number, Time_Status, Train_Date,
  Departure, Destination)
VALUES (:NEW.Train_Number, :NEW.Time_Status, :NEW.Train_Date,
  :NEW.Departure, :NEW.Destination);

So assuming your date column is really called train_date, and you want to check route_status has changed, it will look like:

CREATE OR REPLACE TRIGGER Train_Logging
AFTER UPDATE OF Route_Status ON Routes
FOR EACH ROW
WHEN (new.route_status = 'Arrived' AND old.route_status = 'En-Route')
DECLARE
  logging_flag Configuration.value%type;
BEGIN
  SELECT Configuration.value
  INTO logging_flag
  FROM Configuration
  WHERE Name = 'Enable_Logging';

  IF (logging_flag = 'T') THEN
    INSERT INTO Records (Train_Number, Time_Status, Train_Date,
      Departure, Destination)
    VALUES (:NEW.Train_Number, :NEW.Time_Status, :NEW.Train_Date,
      :NEW.Departure, :NEW.Destination);
  END IF;
END Train_Logging;
/

With some made-up data, and guessing some of your data types:

insert into configuration values ('Enable_Logging', 'T');
insert into routes values (1, 'En-Route', 'On-time', trunc(sysdate),
  trunc(sysdate) + 10/24, 'New York');
update routes set route_status = 'Arrived' where train_number = 1;
select * from records;

TRAIN_NUMBER TIME_STATUS TRAIN_DATE DEPARTURE DESTINATION
------------ ----------- ---------- --------- -----------
           1 On-time     29-APR-14  29-APR-14 New York

Incidentally, keeping the status values as strings makes them harder to manage as you have to always enter everything with exactly the right case. It would be better, generally, to have a look-up table of status codes with a numeric primary key, and have these tables with a foreign-key to that master status table. Then you insert the key value instead of the text, so you don't have to worry about the case etc., and if the wording ever changes you only have to update it in one place. Of course, you have to join to that table to retrieve the text values, but that's not a great hardship.

Upvotes: 1

Related Questions