Copy a record via trigger

  • Hey All,

    We're trying to copy a 'mail' record to another person while retaining the original record in the original recipient's mailbox. There is only 1 Key (the PK, or course) on the column 'ML_ID'. That's not included in the select list or insert because its a PK. The 3 in the nested select is the ID for the admin account.

    Now, the below DOES in fact copy the record and retains the original, but also copies all previous mails that fit the where clause parameters. For example, we have users 1 and 2, and 'mails' with an id of a, b, and c.

    Mail A hits user 1 and the trigger is set to send to user 2:

    MAIL/User

    a 1

    a 2

    Mail B hits user 1:

    MAIL/User

    a 1

    a 2

    b 1

    a 2

    b 2

    Mail C hits user 1:

    MAIL/User

    a 1

    a 2

    b 1

    a 2

    b 2

    c 1

    a 2

    b 2

    c 2

    Hopefully you get the idea...

    Now, I thought the issue might be due to recursion, but it really doesn't seem to be triggering itself, more of just copying more than what I want it to.

    "

    WITH EXECUTE AS CALLER

    AFTER INSERT

    AS

    BEGIN

    IF trigger_nestlevel() > 1 RETURN

    INSERT INTO TRN_MAILS (

    ML_SENDER_TYPE,ML_SENDER_ID,ML_SENDER_NAME,ML_RECEIVER_ID,

    ML_RECEIVER_NAME,ML_MAIL_TYPE,ML_PATIENT_ID,ML_ENCOUNTER_ID,

    ML_SEND_DATE,ML_READ_DATE,ML_MESSAGE,ML_OBJECT_ID,ML_EVENT_ID,

    ML_BOOL_DEL_IN,ML_BOOL_DEL_OUT,ML_SUBJECT,ML_BOOL_READ,ML_MOD_USER,

    ML_MOD_TIMESTAMP,ML_BOOL_ADD_PMR,ML_ACTION_ID,ML_REF_ID,

    ML_BOOL_OPEN,ML_REPLY_DAYS,ML_REPLY_BYDATE,ML_CLOSE_DATE,

    ML_BOOL_THREAD_ID,ML_PENDING,ML_PATIENT_FNAME,ML_PATIENT_LNAME,

    ML_BATCH_NO,ML_DEL_IN_DATE,ML_BOOL_ACCEPT,ML_ACC_DONE_ID,ML_ATTACHMENTS,

    ML_FWD_REPLY,ML_SEND_TZ_DATE,ML_READ_TZ_DATE

    )

    SELECT

    ML_SENDER_TYPE,ML_SENDER_ID,ML_SENDER_NAME,3,

    ML_RECEIVER_NAME,ML_MAIL_TYPE,ML_PATIENT_ID,ML_ENCOUNTER_ID,

    ML_SEND_DATE,ML_READ_DATE,ML_MESSAGE,ML_OBJECT_ID,ML_EVENT_ID,

    ML_BOOL_DEL_IN,ML_BOOL_DEL_OUT,ML_SUBJECT,ML_BOOL_READ,ML_MOD_USER,

    ML_MOD_TIMESTAMP,ML_BOOL_ADD_PMR,ML_ACTION_ID,ML_REF_ID,

    ML_BOOL_OPEN,ML_REPLY_DAYS,ML_REPLY_BYDATE,ML_CLOSE_DATE,

    ML_BOOL_THREAD_ID,ML_PENDING,ML_PATIENT_FNAME,ML_PATIENT_LNAME,

    ML_BATCH_NO,ML_DEL_IN_DATE,ML_BOOL_ACCEPT,ML_ACC_DONE_ID,ML_ATTACHMENTS,

    ML_FWD_REPLY,ML_SEND_TZ_DATE,ML_READ_TZ_DATE

    FROM TRN_MAILS

    WHERE ML_EVENT_ID IN (36,40,22,20,45)

    AND ML_RECEIVER_ID = 1448

    SET NOCOUNT OFF

    END

    "

    Does anyone have any insight into this? Apologies if I've not made something clear. Thanks!

  • You should be using the INSERTED virtual table instead of pulling the data from the original table. Every time the trigger is run, you're copying all mails that were ever sent to that user. If you use the INSERTED virtual table, you'll only be copying mails that were just inserted.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • in other words, in your query

    select ...

    from inserted i

  • Piling on

    IF trigger_nestlevel() > 1 RETURN

    Are you using nested triggers?

    😎

    SET NOCOUNT OFF

    Don't need to do this, it has a session scope so useless.

  • I believe that the inserted table worked just fine. Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply