• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 588
  • Last Modified:

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_BASE AS DEN
INNER JOIN inserted ON DEN.CHART = inserted.strPatientid
0
running32
Asked:
running32
  • 2
  • 2
1 Solution
 
rafranciscoCommented:
Try putting the code inside a BEGIN...END block and see if it will work.

Create Trigger Consent_Date
ON tblPatient
FOR UPDATE
AS
IF UPDATE(dtmEligExpires)
BEGIN
    SET XACT_ABORT ON
    UPDATE    DEN
    SET              CONSENTDATE = inserted.dtmEligExpires
    FROM BCSQL.dentrix.dbo.DDB_PAT_BASE AS DEN
    INNER JOIN inserted ON DEN.CHART = inserted.strPatientid
END

0
 
running32Author Commented:
Thanks that is great......

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?
0
 
rafranciscoCommented:
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.
0
 
running32Author Commented:
Thanks, great help....
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now