I want to write a trigger which updates some fields in another master table record in TRN_PT_TESTS_DET table gets updated for a specific test.
EG. when driving license in PTD_READING_TEXT column is updated in TRN_PT_TESTS_DET table, we want to update PT_DRIVER_LIC column in MST_PATIENT where PT_ID = ENC_PT_ID, which will also look in TST_CODE = 'SC32105' in MST_TESTS table (code 'SC32105' stands for driving license).
Reference query:
select top 10 ENC_PT_ID, PTD_READING_TEXT
from trn_pt_tests_Det
inner join trn_pt_tests_head on ptd_pth_id = pth_id
inner join trn_encounters on pth_enc_id = enc_id
where ptd_test_id = (select TST_ID from MST_TESTS where TST_CODE = 'SC32105');
While doing that we also want to add a record in TRN_NOTES with TN_TYPE = 'PC', TN_OBJECT_ID = PT_ID and TN_NOTE =
"Updated Patient <PT_LAST_NAME>, <PT_FIRST_NAME> Driver License Old:XXX New:YYYY"
I have created a below trigger which is not updating the PT_DRIVER_LIC column in MST_PATIENT and also inserting incorrect record into TRN_NOTES table.
Can someone please tell me what I am doing wrong?
When I execute below update query.
update TRN_PT_TESTS_DET
set PTD_READING_TEXT = 'aaaaa'
where PTD_TEST_ID = 11025
and PTD_PTH_ID = 134
the value is updated and trigger is called but @pt_id, @enc_id, @pt_last_name and @pt_first_name declared variable in trigger fetch different ID's and insert different values in TRN_NOTES table and the update MST_PATIENT table part don't work at all.
Can some please review below trigger and help me to fin out what I am doing wrong.
Trigger:
CREATE TRIGGER update_driver_lic_and_notes
ON TRN_PT_TESTS_DET
AFTER UPDATE
AS
IF (UPDATE (PTD_READING_TEXT))
BEGIN
IF EXISTS (SELECT 1
FROM trn_pt_tests_Det
INNER JOIN trn_pt_tests_head ON ptd_pth_id = pth_id
INNER JOIN trn_encounters ON pth_enc_id = enc_id
WHERE PTH_ID = PTD_PTH_ID
AND EXISTS (SELECT 1
FROM MST_TESTS
WHERE TST_CODE = 'VTWTLBS'))
BEGIN
DECLARE @old_dl varchar(255);
DECLARE @new_dl varchar(255);
DECLARE @pt_id int;
DECLARE @enc_id int;
DECLARE @pt_last_name varchar(255);
DECLARE @pt_first_name varchar(255);
SELECT @old_dl = PT_DRIVER_LIC
FROM MST_PATIENT
INNER JOIN trn_encounters ON PT_ID = ENC_PT_ID
INNER JOIN trn_pt_tests_head ON pth_enc_id = enc_id
INNER JOIN TRN_PT_TESTS_DET ON ptd_pth_id = pth_id
WHERE ptd_test_id = (SELECT TST_ID FROM MST_TESTS
WHERE TST_CODE = 'VTWTLBS');;
SELECT @new_dl = PTD_READING_TEXT,
@pt_id = ENC_PT_ID,
@enc_id = enc_id,
@pt_last_name = PT_LNAME,
@pt_first_name = PT_FNAME
FROM MST_PATIENT
INNER JOIN trn_encounters ON PT_ID = ENC_PT_ID
INNER JOIN trn_pt_tests_head ON pth_enc_id = enc_id
INNER JOIN TRN_PT_TESTS_DET ON ptd_pth_id = pth_id
WHERE ptd_test_id = (SELECT TST_ID FROM MST_TESTS
WHERE TST_CODE = 'VTWTLBS');
-- IF (@old_dl != @new_dl)
UPDATE MST_PATIENT
SET PT_DRIVER_LIC = @new_dl
WHERE PT_ID = @pt_id;
INSERT INTO TRN_NOTES (TN_TYPE, TN_OBJECT_ID, TN_ENC_ID, TN_MOD_USER, TN_NOTES, TN_MOD_TIMESTAMP)
VALUES ('PC', @pt_id, @enc_id,0, 'Updated Patient ' + @pt_last_name + ', ' + @pt_first_name + ' Driver License Old:' + @old_dl + ' New:' + @new_dl, GETDATE());
END
END
Thank you.