CODEWITHSUNDEEP
oracle-databasetriggers
user2534560
user2534560

Reputation: 21

create trigger for copying values between tables in oracle

I am new to the sql. I want to create a trigger for copying values between tables. basically, the task I want to finish is forwarding students' message table values to specific staff_maibox
here is the code.

drop trigger forward_msg_to_staff;

create or replace trigger forward_msg_to_staff
update on message 
for each row
declare
  message_id    VARCHAR2(10);
  client_id     NUMBER(10);
  staff_id      NUMBER(5);
  message_date  DATE;
  message_title VARCHAR2(20);
  staff_mailbox VARCHAR2(255);
begin
  insert into staff_mailbox(message_id, client_id, staff_id, message_date, message_title, staff_mailbox)
    values(:new.message_id, :new.client_id, :new.staff_id, :sysdate, :new.message_title, :old.staff_mailbox)
end;
/

is this code correct? Please advise. thanks in advance.

Upvotes: 1

Views: 117

Answers (1)

Ben
Ben

Reputation: 52893

You're getting an error because you're missing either the BEFORE or AFTER keyword from the CREATE TRIGGER statement.

These are required as indicated in the documentation:

enter image description here

Additionally:

  • There's no need to declare all the variables, you're not using them
  • :sysdate is incorrect, you're not binding it. You can just use sysdate instead as you would in standard SQL or PL/SQL.
  • You're missing a semi-colon after the VALUES clause of the INSERT statement.

Putting this together your trigger may look like this

create or replace trigger forward_msg_to_staff
 after update on message 
 for each row
begin
  insert into staff_mailbox( message_id, client_id, staff_id, message_date 
                           , message_title, staff_mailbox )
  values ( :new.message_id, :new.client_id, :new.staff_id, sysdate
         , :new.message_title, :old.staff_mailbox );
end forward_msg_to_staff;
/

Note that I've used the trigger name in the END as well. This is for convenience only, it makes it obvious where the trigger ends...

If you want to see what errors your're getting when you're creating a trigger use show errors as a_horse_with_no_name suggests. This shows any compilation errors, which is invaluable for tracking them down.

Upvotes: 3

Related Questions