CODEWITHSUNDEEP
sqlplsqltriggers
Kudayar Pirimbaev
Kudayar Pirimbaev

Reputation: 1320

PL/SQL: trigger error on altering insert data

I'm trying to write trigger for the table of books. For this table,

create table books (
    isbn VARCHAR(13) CHECK (LENGTH (isbn) = 10 or LENGTH (isbn) = 13),
    ...
    PRIMARY KEY (isbn)
);

I want to write a trigger that when isbn of length 10 is to be inserted, change its format to the 13-digit one according to the following rules:

  1. Last digit is removed
  2. Added 978 to front

  3. Added check bit that is calculated by the formula

    CHECK_BIT = (10 - (x1 + 3*x2 + x3 + 3*x4 + ... + x11 + 3*x12) mod 10) mod 10

Code:

create or replace trigger isbnFormatChange
before insert on books
for each row
begin
  if (length (:new.isbn) = 10) then
    :new.isbn := substr (:new.isbn, 1, 9);
    :new.isbn := concat (978, :new.isbn);
    :new.isbn := concat (:new.isbn, mod ((10 - mod ((to_number (substr (:new.isbn, 1, 1)) + 3 * to_number (substr (:new.isbn, 2, 1)) + to_number (substr (:new.isbn, 3, 1)) + 3 * to_number (substr (:new.isbn, 4, 1)) + to_number (substr (:new.isbn, 5, 1)) + 3 * to_number (substr (:new.isbn, 6, 1)) + to_number (substr (:new.isbn, 7, 1)) + 3 * to_number (substr (:new.isbn, 8, 1)) + to_number (substr (:new.isbn, 9, 1)) + 3 * to_number (substr (:new.isbn, 10, 1)) + to_number (substr (:new.isbn, 11, 1)) + 3 * to_number (substr (:new.isbn, 12, 1))), 10)), 10));
  end if;
end;

but it gives the following error:

Error(5,5): PL/SQL: Statement ignored
Error(5,63): PLS-00330: invalid use of type name or subtype name

I think I did step 3 wrong (formula part)

Upvotes: 0

Views: 125

Answers (1)

Dmitriy
Dmitriy

Reputation: 5565

Function convert is used for converting different character types to each other. To convert character string to number use function to_number. So you just need to replace in your code all convert (int, ...) with to_number() function:

create or replace trigger isbnFormatChange
before insert on books
for each row
begin
  if (length (:new.isbn) = 10) then
    :new.isbn := substr (:new.isbn, 1, 9);
    :new.isbn := concat (978, :new.isbn);
    :new.isbn := concat (:new.isbn, mod ((10 - mod ((to_number(substr (:new.isbn, 1, 1)) + 3 * to_number(substr (:new.isbn, 2, 1)) + to_number(substr (:new.isbn, 3, 1)) + 3 * to_number(substr (:new.isbn, 4, 1)) + to_number( substr (:new.isbn, 5, 1)) + 3 * to_number( substr (:new.isbn, 6, 1)) + to_number(substr (:new.isbn, 7, 1)) + 3 * to_number( substr (:new.isbn, 8, 1)) + to_number( substr (:new.isbn, 9, 1)) + 3 * to_number( substr (:new.isbn, 10, 1)) + to_number(substr (:new.isbn, 11, 1)) + 3 * to_number( substr (:new.isbn, 12, 1))), 10)), 10));
  end if;
end;
/

convert function in documentation: http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions027.htm#SQLRF00620
to_number function in documentation: http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions211.htm#SQLRF06140

Upvotes: 1

Related Questions