Trigger to Update Foreign Keys (1 Viewer)

[mstorer]

I have searched the internet high and low for a quick solution but so far have become more dumbfounded than when I began. I have found this forum to be the most responsive and beneficial for all of my Access questions so here goes.

I have an Access 2000 front end connected to an IBM DB2 back end. I created my primary and foreign keys through my CREATE TABLE SQL and am enforcing referential integrity through DB2. Unfortunately, I need to write a trigger to enforce UPDATE CASCADE on my foreign keys. The primary key in my parent table is POLICYNUMBER. A corresponding child record may exist in any or all of my three child tables. Should the POLICYNUMBER be mistyped or need to be altered after the records are initially saved, this change clearly needs to cascade to my child tables' Foreign Keys.

Does anyone have the SQL statment to place in my trigger to accomplish this cascading update? I would assume that a SQL Server trigger would, for the most part, be compatible with DB2. For the sake of simplicity, I use the following schematic.

TableA = Parent
TableB = Child
TableC = Child
TableD = Child

I have named the primary and foreign keys POLICYNUMBER.

Since I can be somewhat dim witted, I am looking for an actual example with which I can simply change the table and field names. Any help (or simply pointing me to a solution) would be greatly appreciated. Thanks again.

Matthew

 

[WayneRyan]

Matthew,

On the form where you display the parent table:

1) Use an AfterUpdate event on the PolicyNumber control to
trap when it is changed.

2) Put some code in like this

' **************************************
Dim dbs As Database
Dim SQL As String
'
'
'
Set dbs = CurrentDb
'
' If old value is null, then it is a new entry and do nothing
'
If IsNull(Me.PolicyNumber.OldValue) Then
Exit Sub
End If
'
' Update Children
'
SQL = "Update tblChild1 " & _
"Set PolicyNumber = '" & Me.PolicyNumber.Value & "' " & _
"Where PolicyNumber = '" & Me.PolicyNumber.OldValue & "'"
dbs.execute(strSQL)
'
' Repeat the above pair for each child
'
' **************************************

Notes:

You might check into the On Change, Before Update events.

PolicyNumber is treated as a string here which is the reason
for the single-quote delimiters.

The SQLs will not return any confirmation information. You will
not know if 1000 rows are updated, or none at all.

hth,
Wayne

 

[mstorer]

Thanks immensely for the feedback. I will give this a try as soon a possible. I'll admit, this is a different approach than what I had expected. What I was expecting as something like the following:

CREATE TRIGGER POLICY NUMBER UPDATE
AFTER UPDATE OF POLICY NUMBER
ON TABLEA
FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC........

I was under the impression that one needs all aspects of referential integrity handled by the DBMS rather than on the client/front end program. That is why I was going about it on the back end. As long as no one feels that I would be breaking a rule of RI, I will be more than happy to enforce it programatically on the front end.

Wayne, thanks again for your great directions. I will let you know how it goes.

- Matthew

 

[Pat Hartman]

You should change your primary key to one that will not change. Add a unique index on POLICYNUMBER so no duplicates will be allowed. That will leave RI with the database engine and eliminate the need for a trigger.

Under NO condition would I allow an Access form to implement RI rules when I have the option of creating a database trigger. Jet does not support triggers so with an Access backend you would not have any options but, with a DB2 backend, you do have options.