Oracle Trigger – Assign Custom ID Using Sequence

oraclesequencetrigger

i want to modify my current table which is currently generating id's using a trigger from 1 using sequence_name.nextval to something like DON-100 or REC-124 etc.I have seen some examples doing this using functions which modify the values after the trigger performs it's job which looks like overhead how can i modify my current trigger to produce these custom id's and is not too complex.

CREATE TABLE Atendant (
   ID NUMBER(10) PRIMARY KEY,
   NAME  VARCHAR2(25 CHAR) NOT NULL
);

CREATE SEQUENCE  Atendant_SEQ  NOCACHE;

CREATE or replace TRIGGER  Atendant_BI 
  BEFORE INSERT ON  Atendant 
  FOR EACH ROW
DECLARE
BEGIN
  IF :NEW.ID  IS NULL THEN
    :new.id := "ATN"Atendant_SEQ.nextval;
  END IF;
END;

Best Answer

You're nearly there! A few small changes needed.

Table definition:

CREATE TABLE Atendant (
   ID VARCHAR(20) PRIMARY KEY,
   NAME  VARCHAR2(25 CHAR) NOT NULL
);

Trigger:

CREATE or replace TRIGGER  Atendant_BI 
  BEFORE INSERT ON  Atendant 
  FOR EACH ROW
DECLARE
BEGIN
  IF :NEW.ID  IS NULL THEN
    :new.id := 'ATN'||Atendant_SEQ.nextval;
  END IF;
END;
/

Test:

SQL> insert into Atendant (NAME) values ('phil');

1 row created.

SQL> select * from Atendant;

ID     NAME
------ ----------
ATN1   phil

SQL>

So all you were really missing was the data type and doing the concatenation correctly.

Related Solutions

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

What you presented is essentially the correct way for interrupting triggers in MySQL 5.1

I wrote about this before

Dec 23, 2011 : check constraint does not work?
Apr 25, 2011 : Trigger in MySQL to prevent insertion

You cannot do Dynamic SQL in triggers.

You may have to resort to coding the my_signal like this:

CREATE PROCEDURE `my_signal`(error_type INT)
BEGIN
    IF error_type = 1 THEN
        UPDATE `Error: can not insert new OWNER !` set x=1;
    END IF;
    IF error_type = 2 THEN
        UPDATE `Error: Row can not reference itself!` set x=1;
    END IF;
    .
    .
END//

then change the trigger to look like this:

DELIMITER $$
CREATE
  TRIGGER `employee_before_insert` BEFORE INSERT
    ON `Employee`
    FOR EACH ROW BEGIN
      CASE
       WHEN NEW.designation = 'OWNER'  THEN
          CALL my_signal(1);
       WHEN NEW.SSN = NEW.MSSN THEN
          CALL my_signal(2);
      END CASE; 
  END$$   
DELIMITER ;

Sorry for the clumsy programming but Signal Processing is MySQL 5.1 is horrible.

How to commit transaction on an after update event trigger

Why do you expect B to be updated, when you inserted a row with a different id? Actually, your INSERT ALL does not insert anything into B.

The above works fine after fixing your trigger and sample data:

create or replace trigger updB  
after update on B  
for each row  
declare  
v varchar2(5);  
begin  
  v := :new.val;
end;  
/

This is how you reference the new val in the trigger, not with a select. Your original trigger would run into the infamous ORA-04091 "table is mutating" error.

SQL> insert all into A values ('a', 'false') into b values ('a', 'false') select * from dual;

2 rows created.

SQL> commit;

Commit complete.

SQL> update A set val = 'true';
Cek cek: false

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from a;

ID         VAL
---------- ----------
a          true

SQL> select * from b;

ID         VAL
---------- ----------
a          true

For the output:

Your updA trigger runs in an autonomous transaction. When you select val from A, your original update is still not finished (commited), you will see the old value. Fixing your trigger for A:

create or replace trigger updA  
after update on A  
for each row  
declare  
pragma autonomous_transaction;  
cek varchar2(5);  
begin  
commit;  
  cek := :new.val;  
dbms_output.put_line('Cek cek: ' || cek);  
update B set val = :new.val where id = :new.id;  
commit;  
end;  
/

Best Answer

Related Solutions

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

How to commit transaction on an after update event trigger

Related Question