asked on

SQL trigger to update data in another table

I have a trigger that clears data values in the table if the date of surgery changes. It works but now they have to have data in another table cleared with the same rule. There is a third table that is needed to create the relationship but no data is updated in that table.

Here is the trigger as it is now

IF UPDATE(DateSurgery)
      BEGIN
            UPDATE dbo.Patient
            SET Patient.AnesStartTime = NULL,
                  Patient.InRoomTime = NULL,
                  Patient.TOTSTime = NULL,
                  Patient.OutRoomTime = NULL,
                  Patient.AnesEndTime = NULL,
                  Patient.FinalTimeOutTime = NULL,
                  Patient.StartProcTime = NULL,
                  Patient.EndProcTime = NULL,
                  Patient.NursingStartTime = NULL,
                  Patient.NursingEndTime = NULL
            FROM INSERTED
            WHERE Patient.PatientID = Inserted.PatientID
      END

Here is the relationship that connects the tables with the data that has to also be cleared.

SELECT ORProc.EndOpTime, Patient.EndProcTime, ORProc.IncisionTime, Patient.StartProcTime, Patient.DateSurgery
FROM Service INNER JOIN
Patient ON Service.PatientID = Patient.PatientID INNER JOIN
ORProc ON Service.ServiceID = ORProc.ServiceID

The ORProc.EndOpTime and ORProc.IncisionTime has to also be null if date of surgery is updated.

Any help is appreciated.

John

ste5an

Please edit your post and use the CODE button or the [code][/code] tags to embed code into your post. This increases readability and makes copying code simpler.

Then I doubt, that your trigger really does what you thing is correct. I'm still thinking that you're using a incorrect data model. You should think in terms of a paper file. Just because the date of surgery was changed, this does not mean that you shred the old file. Consider the case where the anesthesia was started, but then for some reasons the entire surgery was stopped and is now rescheduled. We have here a classic case of immutable data, when its once entered for audit reasons. Rescheduling a surgery means that you need to create a new surgery file, not overwriting the old.

Besides that, you haven't taken your time to understand what I've written in your other thread. UPDATE() will give you false positives. You need to test the old value:

IF UPDATE(DateSurgery)
BEGIN
    UPDATE P
    SET    P.AnesStartTime = NULL ,
           P.InRoomTime = NULL ,
           P.TOTSTime = NULL ,
           P.OutRoomTime = NULL ,
           P.AnesEndTime = NULL ,
           P.FinalTimeOutTime = NULL ,
           P.StartProcTime = NULL ,
           P.EndProcTime = NULL ,
           P.NursingStartTime = NULL ,
           P.NursingEndTime = NULL
    FROM   dbo.Patient P
           INNER JOIN INSERTED I ON P.PatientID = I.PatientID
           INNER JOIN DELETED D ON P.PatientID = D.PatientID
    WHERE  D.DateSurgery != I.DateSurgery;
END;

Open in new window

And for what you want to delete: You model is not clear. But as also written by pcelba in your other thread, DON'T USE TRIGGERS here. If this logic is necessary, which I still doubt that it could be correct at all, then implement it in a stored procedure.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JohnMac328

ASKER

Ste5an - This is for post op data only, the surgery has already been done but sometimes it needs to be corrected so they want to force people to enter new data.

ste5an

Well, as I said, but then you should use a stored procedure. Not a trigger.

Especially as I can imagine that changing the date should be a double-entry at to confirm the change and the deletion of existing data. Thus having a procedure just supports the logic in a semantic kind of fashion as well by the terms of code flow.

JohnMac328

ASKER

Ste5an I am going to try a stored procedure but give credit to Scott because he answered with a trigger. I will prob be back anyway with another question.

Thanks

JohnMac328

ASKER

Thanks!

ste5an

USE tempdb;
GO

DROP TABLE IF EXISTS Test;

CREATE TABLE Test (
    ID INT IDENTITY NOT NULL PRIMARY KEY ,
    Payload INT NULL ,
    ChangeDate DATE NOT NULL
);
GO

CREATE TRIGGER tr_Test_U
ON dbo.Test
AFTER UPDATE
AS
SET NOCOUNT ON;

IF UPDATE(ChangeDate)
BEGIN
    UPDATE T
    SET    T.Payload = NULL
    FROM   Test T
           INNER JOIN INSERTED I ON I.ID = T.ID;
END;
GO

INSERT INTO Test ( Payload ,
                   ChangeDate )
VALUES ( 1, '2020-01-01' ) ,
       ( 2, '2020-01-01' ) ,
       ( 3, '2020-01-01' );

SELECT *
FROM   Test;

UPDATE dbo.Test
SET    ChangeDate = ChangeDate;

SELECT *
FROM   Test;

Open in new window

Again, UPDATE() does only test, whether the specified column was used in an UPDATE statement. But it does not test for an actual value change.

JohnMac328

ASKER

I have been searching for stored procedure to update two tables and I get many people saying to use a trigger *sigh and others saying it can't be done or should not do it. I will ask another question so you can answer it.

Scott Pletcher

Gosh, why would you issue an UPDATE statement just to update the date back to itself? Hmm, well, you wouldn't.

However, if you are going to test the values to see if they differ then the code you posted earlier:

WHERE D.DateSurgery != I.DateSurgery;

is completely inadequate / wrong for that purpose. Don't you test anything? The DateSurgery value will start as as NULL value, which the condition above will NOT pass thru when the column is first UPDATEd to a non-NULL value, which means for the first assignment of the value your proposed trigger code will definitely fail to run. Similarly, if the Date is changed to a NULL -- perhaps to void out a surgery -- your trigger would also again fail to detect that and would not run.

Your code checks only for the most illogical thing, "changing" the value to itself. I'd rather not check than use that check, although a full, accurate check would likely be better than no check at all.

JohnMac328

ASKER

First I have been arguing with the programmer that wants this. That being said there will always be a date in the post op record. If the date is changed they want the fields in the table cleared so they are forced to enter new data. That all works with the original trigger but i ran into problems when they introduced the second table to be updated also.

JohnMac328

ASKER

Gosh, does anyone test things here? Without test for value change, your trigger doesn't do what you expect, cause you make assumptions on how data is accessed. E.g. a case where the ChangeDate is not changed

ste5an - When a record is brought up in the post op section, they have to click on a link to change the date. When that date is changed they want the user forced into entering new data. It did work fine until they added the second table to the mix.

Scott Pletcher

I figured something like that. That's why I didn't q it, and wrote the trigger using the original conditions. I assume people know more about their own data than I do.

IF in fact such an issue was going to come up, it would have done it by now and you'd have addressed it earlier.

ste5an

Well, maybe it's because I'm not a native English speaker, but in German "changing the date" is not equal to "update to the same value".

That's just the difference between a working and a correct approach.

When a record is brought up in the post op section, they have to click on a link to change the date. When that date is changed they want the user forced into entering new data. It did work fine until they added the second table to the mix.

This is an UI / work flow requirement above the table level of a relational database and should be implemented there.

ste5an

Don't you test anything? The DateSurgery value will start as as NULL value, which the condition above will NOT pass thru when the column is first UPDATEd to a non-NULL value, which means for the first assignment of the value your proposed trigger code will definitely fail to run.

According to given description, those values are depended of a surgery date, thus they cannot exist before it was entered. Furthermore the requirement was "when date is changed". NULL is not a date, we have tri-state logic here. And that column is confirmed to be NOT NULL.

Similarly, if the Date is changed to a NULL -- perhaps to void out a surgery -- your trigger would also again fail to detect that and would not run.

That's exactly was I was talking about, the data model is flawed.

Scott Pletcher

Furthermore the requirement was "when date is changed". NULL is not a date

C'mon, that's sophistry, trying to dodge clear logic. A change from NULL to a valid date is still a change of date (it must be: if the date was unchanged, it would still be NULL!).

The date would have to have been NULL before it was filled in the first time, right?!

JohnMac328

ASKER

Once again it works when the date is changed.

JohnMac328

ASKER

Scott - would it also be possible to compare datesurgery with EndOpTime and IncisionTime to check if they are equal?

Scott Pletcher

Sure. The direct compare would be:

WHERE I.DateSurgery IN (ORP.EndOpTime, ORP.IncisionTime)

Seems more likely you might want to change the DateSurgery only it's not already equal to one of them (?!), in which case it's:

WHERE I.DateSurgery NOT IN (ORP.EndOpTime, ORP.IncisionTime)

UPDATE ORP
SET EndOpTime = NULL,
IncisionTime = NULL
FROM INSERTED I
INNER JOIN Service S ON S.PatientID = I.PatientID
INNER JOIN ORProc ORP ON ORP.ServiceID = S.ServiceID
WHERE I.DateSurgery NOT IN (ORP.EndOpTime, ORP.IncisionTime)

JohnMac328

ASKER

Great thanks!