SQL Update trigger requirement

32 Views Asked by At

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.

0

There are 0 best solutions below