putting a select statement in a trigger
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: putting a select statement in a trigger

Hybrid View

  1. #1
    Join Date
    Apr 2009
    Posts
    10

    putting a select statement in a trigger

    I'm trying to use as select statment in a trigger to get info into another table when an update occurs on the table with the trigger, along with a couple constants. I can't get the pl/sql to work, any ideas on what I'm doing wrong? Thanks.

    if (updating and ld.term_date <> :new.term_date) then
    insert into employee_change (company, employee, emp_status, first_name, last_name, middle_init, nick_name, birthdate, sex, adj_hire_date, date_hired, wk_phone_nbr, wk_phone_ext, pager, email_address, term_date, typeofchange, change_dt, field_changed )
    values (SELECT a.company,
    a.employee,
    a.EMP_STATUS,
    a.FIRST_NAME,
    a.LAST_NAME,
    a.MIDDLE_INIT,
    a.NICK_NAME,
    test.PAEMPLOYEE.BIRTHDATE,
    test.PAEMPLOYEE.SEX,
    a.ADJ_HIRE_DATE,
    a.DATE_HIRED,
    test.PAEMPLOYEE.WK_PHONE_NBR,
    test.PAEMPLOYEE.WK_PHONE_EXT,
    test.hrcontnbr.PHONE AS Pager,
    a.EMAIL_ADDRESS,
    a.term_date
    FROM test.EMPLOYEE a JOIN test.PAEMPLOYEE ON a.COMPANY = test.PAEMPLOYEE.COMPANY AND a.EMPLOYEE=test.PAEMPLOYEE.EMPLOYEE
    left outer join test.HRCONTNBR on a.company = test.HRCONTNBR.COMPANY AND a.EMPLOYEE = test.HRCONTNBR.ID_NBR, 'U', sysdate, 'term_date');
    end if;

  2. #2
    Join Date
    Apr 2009
    Posts
    10

    further info

    My real problem is getting info from two other tables. I could just use
    values (ld.company, ld.employee, etc. for the info from the table that the trigger is on (test.employee), but how would I get the info from the other two tables?
    Thanks very much for any ideas.

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Unfortunatelly you forgot to show the trigger definition.

    Query appears not to reference any value on trigger affected table - other tables in query should be references as in any normal query.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Apr 2009
    Posts
    10
    Is it just a syntax problem then? Thanks!

    CREATE OR REPLACE TRIGGER DMCMILLAN.test_change_employee
    AFTER INSERT OR UPDATE
    ON TEST.EMPLOYEE
    REFERENCING NEW AS New OLD AS Old
    FOR EACH ROW

    /******************************************************************************
    NAME: test_change_employee
    PURPOSE:

    REVISIONS:
    Ver Date Author Description
    --------- ---------- --------------- ------------------------------------
    1.0 10/13/2011 dmcmillan 1. Created this trigger.

    NOTES:

    Automatically available Auto Replace Keywords:
    Object Name: test_change_employee
    Sysdate: 10/13/2011
    Date and Time: 10/13/2011, 12:52:18 PM, and 10/13/2011 12:52:18 PM
    Username: dmcmillan (set in TOAD Options, Proc Templates)
    Table Name: EMPLOYEE (set in the "New PL/SQL Object" dialog)
    Trigger Options: (set in the "New PL/SQL Object" dialog)
    ******************************************************************************/
    BEGIN
    if inserting then
    insert into employee_change (company, employee, emp_status, first_name, last_name, middle_init, nick_name, adj_hire_date, date_hired, email_address, typeofchange, change_dt)
    values (:new.company, :new.employee, :new.emp_status, :new.first_name, :new.last_name, :new.middle_init, :new.nick_name, :new.adj_hire_date, :new.date_hired, :new.email_address, 'I', sysdate);
    end if;

    if (updating and ld.last_name <> :new.last_name) then
    insert into employee_change (company, employee, emp_status, first_name, last_name, middle_init, nick_name, adj_hire_date, date_hired, email_address, term_date, birthdate, sex, wk_phone_nbr, wk_phone_ext, pager, typeofchange, change_dt, field_changed )
    values (ld.company, ld.employee, ld.EMP_STATUS, ld.FIRST_NAME, :new.LAST_NAME, ld.MIDDLE_INIT, ld.NICK_NAME, ld.ADJ_HIRE_DATE, ld.DATE_HIRED, ld.EMAIL_ADDRESS, ld.term_date,
    (select ('ld.birthdate') from test.employee, test.paemployee where test.employee.COMPANY = test.PAEMPLOYEE.COMPANY AND test.employee.EMPLOYEE=test.PAEMPLOYEE.EMPLOYEE),
    (select SEX from test.EMPLOYEE a JOIN test.PAEMPLOYEE ON test.employee.COMPANY = test.PAEMPLOYEE.COMPANY AND test.employee.EMPLOYEE=test.PAEMPLOYEE.EMPLOYEE),
    (select WK_PHONE_NBR FROM test.EMPLOYEE a JOIN test.PAEMPLOYEE ON a.COMPANY = test.PAEMPLOYEE.COMPANY AND a.EMPLOYEE=test.PAEMPLOYEE.EMPLOYEE),
    (select WK_PHONE_EXT FROM test.EMPLOYEE a JOIN test.PAEMPLOYEE ON a.COMPANY = test.PAEMPLOYEE.COMPANY AND a.EMPLOYEE=test.PAEMPLOYEE.EMPLOYEE),
    (select PHONE AS Pager FROM test.EMPLOYEE a left outer join test.HRCONTNBR on a.company = test.HRCONTNBR.COMPANY AND a.EMPLOYEE = test.HRCONTNBR.ID_NBR),
    'U', sysdate, 'last_name');
    end if;


    END test_change_employee;



    /

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Yes. Just a syntax/logic issue.

    I'll suggest to test "insert" query alone, once it works it will work inside the trigger.

    By the way, this trigger will add a lot of overhead - hope table doesn't get a lot of insert/updates.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Apr 2009
    Posts
    10
    The insert works ok, but it isn't connecting to the other two tables. I think that's my problem. I'm missing something I need to do to select from the other tables.
    And you are right, it won't run very often.
    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width