running32
asked on
Triggers HELP!!!!
Can someone please tell me how to write a trigger correctly.
I have been trying to write a trigger for about 2 weeks now that will fire when a field is updated in the database from an access form.
The trigger will work when I SET XACT_ABORT ON in the trigger but then the rest of the access application will not work. If I put SET XACT_ABORT OFF at the end of the trigger it will still not work. Any ideas on how I can rewrite this?
thanks
Create Trigger Consent_Date
ON tblPatient
FOR UPDATE
AS
IF UPDATE(dtmEligExpires)
SET XACT_ABORT ON
UPDATE DEN
SET CONSENTDATE = inserted.dtmEligExpires
FROM BCSQL.dentrix.dbo.DDB_PAT_
INNER JOIN inserted ON DEN.CHART = inserted.strPatientid
I have been trying to write a trigger for about 2 weeks now that will fire when a field is updated in the database from an access form.
The trigger will work when I SET XACT_ABORT ON in the trigger but then the rest of the access application will not work. If I put SET XACT_ABORT OFF at the end of the trigger it will still not work. Any ideas on how I can rewrite this?
thanks
Create Trigger Consent_Date
ON tblPatient
FOR UPDATE
AS
IF UPDATE(dtmEligExpires)
SET XACT_ABORT ON
UPDATE DEN
SET CONSENTDATE = inserted.dtmEligExpires
FROM BCSQL.dentrix.dbo.DDB_PAT_
INNER JOIN inserted ON DEN.CHART = inserted.strPatientid
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This clause in your INNER JOIN already does that, update the one record that has been changed
ON DEN.CHART = inserted.strPatientid
No need to put a WHERE clause for that.
Putting it inside the BEGIN ... END will group the statements together. Without the BEGIN...END, if the dtmEligExpires is updated, SET XACT_ABORT ON is executed plus the UPDATE DEN. If there was an update on another column, the UPDATE DEN will still be executed.
ON DEN.CHART = inserted.strPatientid
No need to put a WHERE clause for that.
Putting it inside the BEGIN ... END will group the statements together. Without the BEGIN...END, if the dtmEligExpires is updated, SET XACT_ABORT ON is executed plus the UPDATE DEN. If there was an update on another column, the UPDATE DEN will still be executed.
ASKER
Thanks, great help....
ASKER
Two questions....
What does putting it inside the being and end do?
If I put a where clause in there where WHERE DEN.chart = tblpatient.strPatientid will it only update the one record that has been changed?