Archives of the TeradataForum

Message Posted: Wed, 10 Oct 2001 @ 19:38:21 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

I need help to port our Oracle trigger to Teradata ASAP. I read that you cannot have variable in the trigger to store an value from a 'select' statement. Can someone help me to work around it?

Can I either use macro or multiple triggers to capture the logic below?

Here it is:

CREATE OR REPLACE TRIGGER tr_clickthrough_detail
after insert ON bf_clickthrough_detail
FOR each row
DECLARE
    v_delivered NUMBER;
    v_count NUMBER;
    pragma AUTONOMOUS_TRANSACTION;
BEGIN
    SELECT delivered INTO v_delivered
    FROM bf_mailing_summary
    WHERE mailing_id =  :new.mailing_id;

    UPDATE bf_clickthrough_summary set how_many = how_many + 1,
    tracking_rate = (1.0*(unique_clicks + 1))/v_delivered
    WHERE mailing_id = :new.mailing_id
    AND map_id = :new.map_id;

    IF SQL%NOTFOUND THEN
        RAISE_APPLICATION_ERROR(-20004,  'NO bf_clickthrough_summary record
was found - no update can be performed.');
    END IF;

    UPDATE bf_mailing_summary set total_clicks = total_clicks + 1
    WHERE mailing_id = :new.mailing_id;
    IF SQL%NOTFOUND THEN
        RAISE_APPLICATION_ERROR(-20004,  'NO bf_clickthrough_summary record
was found - no update can be performed.');
    END IF;

    SELECT COUNT(*) INTO v_count
    FROM bf_clickthrough_detail
    WHERE mailing_id = :new.mailing_id
    AND   userid = :new.userid
    AND   when_clicked < :new.when_clicked;

    IF v_count = 0 THEN
        UPDATE bf_mailing_summary
        SET respondents = respondents + 1
        WHERE mailing_id = :new.mailing_id;
    END IF;

    SELECT COUNT(*) INTO v_count
    FROM bf_clickthrough_detail
    WHERE mailing_id = :new.mailing_id
    AND   userid = :new.userid
    AND   map_id = :new.map_id
    AND   sequence = :new.sequence
    AND   when_clicked < :new.when_clicked;

    IF v_count = 0 THEN

        UPDATE bf_mailing_summary
        SET unique_clicks = unique_clicks + 1
        WHERE mailing_id = :new.mailing_id;

        UPDATE bf_clickthrough_summary
        SET unique_clicks = unique_clicks + 1
        WHERE mailing_id = :new.mailing_id
        AND   map_id = :new.map_id
        AND   sequence = :new.sequence;

    END IF;

    COMMIT;

END;
;

Thanks,

Rita

	<Prev	Next>		<<First	<Prev	Next>	Last>>