Creating Oracle SQL Trigger Error

Question

This is what I need to accomplish: Create a TRIGGER named tgr_customer_insert that will fire AFTER a row is inserted into the customers table. The trigger can be created after you create the cardholder table, so it can be in the same ps16a.sql file just created. This trigger will insert a row into the cardholder table when a row is inserted into the temp_table_customers table. Here are the columns to insert: card_number (this is inserted using the seq_cardholder sequence number) customer_id (this is a bind variable from the temp_table_customer table using the :new.column_name syntax) credit_limit (this is a bind variable from the temp_table_customer table using the :new.column_name syntax)

This is my code:

`CREATE OR REPLACE TRIGGER tgr_customer_insert
AFTER INSERT
   ON customers
   FOR EACH ROW
BEGIN
   -- Insert record into customers table
   INSERT INTO cardholder
   ( card_number,
     customer_id,
     credit_limit
   )
   VALUES
   ( new.seq_cardholder,
     :new.customer_id,
     :new.credit_limit
   );
END;

`

Error is: ORA-24344: success with compilation error Line 3 Position 4.

Hair is being torn out. Thank you in advance for you time with this matter.

Detail on error is: PL/SQL: SQL Statement ignored

Flash_Jordan
– Flash_Jordan

2018-05-15 03:26:42 +00:00
Commented May 15, 2018 at 3:26 — Flash_Jordan
– Flash_Jordan, Commented May 15, 2018 at 3:26
Use show errors to get the full error message

user330315
– user330315

2018-05-15 05:48:34 +00:00
Commented May 15, 2018 at 5:48 — user330315
– user330315, Commented May 15, 2018 at 5:48

user8406805 · Accepted Answer · 2018-05-15 03:39:36Z

0

I think you are missing a ':' in INSERT VALUES for first value binding.

CREATE OR REPLACE TRIGGER tgr_customer_insert
AFTER INSERT
   ON customers
   FOR EACH ROW
BEGIN
   -- Insert record into customers table
   INSERT INTO cardholder
   ( card_number,
     customer_id,
     credit_limit
   )
   VALUES
   ( :new.seq_cardholder,
     :new.customer_id,
     :new.credit_limit
   );
END;

If, "seq_cardholder" is a sequence then you have to use as below:

CREATE OR REPLACE TRIGGER tgr_customer_insert
AFTER INSERT
   ON customers
   FOR EACH ROW
BEGIN
   -- Insert record into customers table
   INSERT INTO cardholder
   ( card_number,
     customer_id,
     credit_limit
   )
   VALUES
   ( seq_cardholder.nextval,
     :new.customer_id,
     :new.credit_limit
   );
END;

answered May 15, 2018 at 3:39

user8406805

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

3 Comments

Flash_Jordan Over a year ago

I tried that before posting but I get: ORA-24344: success with compilation error

Flash_Jordan Over a year ago

More details show: PLS-00049: bad bind variable 'NEW.SEQ_CARDHOLDER'

Flash_Jordan Over a year ago

Nevermind, you are correct. It is a sequence that I created and your code created the trigger successfully.

Collectives™ on Stack Overflow

Creating Oracle SQL Trigger Error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related