how to convert trigger 'if inserting' from oracle to mysql

Question

I have been trying to convert trigger from oracle to MySQL/MariaDB. I'm still new in MySQL so I'm still figuring.

This is the table structure from the table that I'm doing the trigger:

CREATE TABLE `attendance` (
  `attendanceid` varchar(10) NOT NULL,
  `studentname` varchar(50) DEFAULT NULL,
  `classname` varchar(20) DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL,
  `atdate` varchar(20) DEFAULT NULL,
  `classid` varchar(20) DEFAULT NULL
)

I've already created a sequence table for the trigger as below :

CREATE TABLE `att_seq` (
  `id` int(11) NOT NULL
)

This is the code from oracle:

CREATE OR REPLACE TRIGGER "STUDENT"."CLASSID" 
BEFORE INSERT ON attendance
FOR EACH ROW
declare
BEGIN
 if :new.attendanceid is null then
                  select 'ATT' || attandance_seq.nextval into :new.attendanceid from dual;

    if inserting then
            if(:new.classname = '4A') then
            :new.classid := 'CLASS401';
            end if;
            if(:new.classname = '4B') then
            :new.classid := 'CLASS402';
            end if;
             if(:new.classname = '4C') then
            :new.classid := 'CLASS403';
            end if;
    end if;
  end if;
END;
/

and this is by far how I have converted it (p/s: the 'update class..' code is a new code that i added from the oracle one):

DELIMITER $$
CREATE TRIGGER att_auto_id
BEFORE INSERT ON attendance
FOR EACH ROW
BEGIN
  INSERT INTO att_seq VALUES (NULL);
  SET NEW.attendanceid = CONCAT('ATT', LPAD(LAST_INSERT_ID(), 3, '00'));
  
  IF inserting THEN
    IF new.classname = `4A` THEN
        SET new.classid = `CLASS401`;
        UPDATE class 
        SET classid = new.classid;
    END IF;
    IF new.classname = `4B` THEN
        SET new.classid = `CLASS402`;
        UPDATE class 
        SET classid = new.classid;
    END IF;
    IF new.classname = `4C` THEN
        SET new.classid = `CLASS403`;
        UPDATE class 
        SET classid = new.classid;
    END IF;
  END IF;
END $$

when I run it, I get this error:

1054 - Unknown column 'inserting' in 'field list'

It would be much appreciated if someone could help me. Thank you in advanced :)

I'm sure you're close. Out of curiosity, does ORACLE mode. LAST_INSERT_ID doesn't look right, its from the previous insert. OLD.pk IS NULL might be the way to determine inserting. — danblack
– danblack, Commented May 27, 2020 at 1:08

Shadow · Accepted Answer · 2020-05-27 01:37:22Z

1

The inserting key word in Oracle triggers helps you to determine if the trigger was invoked by an insert operation because in Oracle you can define a trigger to be invoked by multiple events (e.g. before insert or update).

However, in mysql a trigger can only be invoked by a single event, so there is no need for this key word in mysql. Simply do not have the if inserting then condition in your code.

Also, formatting the sequence value in the table is wrong - either format the value when you display it in your application or use zerofill property when declaring the column. This way you can use an auto_increment column directly in the table.

The declaration of the sequence table is missing the auto_increment property. The field is also declared as not null, so you cannot use null to generate a new auto_increment value. Use either a nullable field or an empty values clause.

Furthermore, I think you should remove all the update class statements as well. Those statements will update the classid field of all records in the class table unconditionally for all records.

edited May 27, 2020 at 1:37

answered May 27, 2020 at 1:30

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

K.G Over a year ago

thank you, it works. also thank you for pointing out my mistake on the update class statements

Collectives™ on Stack Overflow

how to convert trigger 'if inserting' from oracle to mysql

1054 - Unknown column 'inserting' in 'field list'

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1054 - Unknown column 'inserting' in 'field list'

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related