Insert & Update Trigger Problem

Insert & Update Trigger Problem

Post by Chris Cairn » Tue, 28 Sep 1999 04:00:00



Access allows you to create a relationship that is enforced only if the
field is not null.   I have created an update and insert trigger that
functions properly except when the user leaves a field blank.  The update
trigger fires and gives the user an error.  I don't care if the field is
left null as long as when the user does enter a value it matches the an
entry in the related table.  I Need to be able to determine if a field is
null upon insert and then ignore the relationship as long as that field is
null  I have not been able to locate the proper syntax  for this senario.
A sample of something similar would be greatly appreciated....
 
 
 

Insert & Update Trigger Problem

Post by Wayne Snyde » Wed, 29 Sep 1999 04:00:00


In your code, just check the inserted table in the correct column for null
ie.

if inserted.columnval is null return

Otherwise do some work..

--
Wayne Snyder
MCSE, MCT, SQL Server MVP
IKON Education Services -  C*te, NC
(Please respond to newsgroup unles otherwise requested.)

 
 
 

Insert & Update Trigger Problem

Post by Chris Cairn » Wed, 29 Sep 1999 04:00:00


I have tried several ways to do this, however I always get a syntax error. I
am quite new at this so please stick with me.   The example you gave:

if inserted.PmtType is null return

returns the error  ADO: Error The column prefix 'inserted' does not match
with a table name or alias name used in the query.
Here is an example of my code:

Alter TRIGGER Accounts_IPmtTypeTrig ON Accounts FOR INSERT AS
SET NOCOUNT ON
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'PmtType' BUT IGNORES NULL VALUES
*/

if inserted.PmtType is null return
 IF (SELECT COUNT(*) FROM inserted) !=
    (SELECT COUNT(*) FROM PmtType, inserted WHERE (PmtType.PmtType =
inserted.PmtType))
     BEGIN
         RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''PmtType''.'
         ROLLBACK TRANSACTION
     END


Quote:> In your code, just check the inserted table in the correct column for null
> ie.

> if inserted.columnval is null return

> Otherwise do some work..

> --
> Wayne Snyder
> MCSE, MCT, SQL Server MVP
> IKON Education Services -  C*te, NC
> (Please respond to newsgroup unles otherwise requested.)

 
 
 

1. Create Trigger - Insert, Update - not updating record on Insert

When entering a value into the field for the first time on
an Insert, the trigger does not fire.

If you modify data in the field with the trigger, the
trigger will fire.

Cannot determine why the trigger will not fire.  No errors
are generated.

Win 2000 Professional, SQL 2000 SP2

  trSLA_Hours.txt
1K Download

2. Idea: recycle WAL segments, don't delete/recrea

3. puzzling issue with inserted, updated and IF UPDATE(column) in an update trigger

4. cluster pour le divx

5. SS7 / update & insert triggers / dynamic query

6. WANTED : Info on Image Databases

7. INSERT & UPDATE TRIGGERS ON SAME TABLE

8. Max value of RecordID?

9. Update & Insert Trigger

10. INSERT trigger versus UPDATE/DELETE trigger

11. Trigger: UPDATE INSERT Problem

12. Update Trigger and ChildID Insert Trigger

13. SQL 6.5 SP5 Problem for Insert Trigger Update Function