October 18, 2016 at 11:51 am
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!
October 18, 2016 at 12:58 pm
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
October 18, 2016 at 1:34 pm
in other words, in your query
select ...
from inserted i
October 18, 2016 at 2:31 pm
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.
October 18, 2016 at 2:32 pm
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