this Snowflake is "Triggered!" (1 Viewer)

madEG

Registered User.
Local time
Today, 13:48
Joined
Jan 26, 2007
Messages
309
Hello,

I had a user call me saying that her off-the-shelf access app is broken.

Basically, there is a trigger that checks on several relationships before deleting a contact record so as to not strand orphan records. Ok, cool... but? The below code in the delete trigger ends up with the below message (shown in the image), and is not exactly elegant.

Code:
/* * PREVENT DELETES IF DEPENDENT RECORDS IN 'tblCommitteeMembership' */

IF (SELECT COUNT(*) FROM deleted, tblCommitteeMembership WHERE (deleted."Person ID" = tblCommitteeMembership."Person ID")) > 0
    BEGIN
        RAISERROR 44445 'The record can''t be deleted or changed. Since related records exist in table ''tblCommitteeMembership'', referential integrity rules would be violated.'
        ROLLBACK TRANSACTION
    END
How would I get the error message to show what is written in the trigger? I've never tried this before... so forgive my newbieness :)

Thoughts? Tips? Pushes in the right direct?

Thanks! :)

-Matt G.
 

Attachments

  • New SQL Error.JPG
    New SQL Error.JPG
    31.4 KB · Views: 128

madEG

Registered User.
Local time
Today, 13:48
Joined
Jan 26, 2007
Messages
309
...sorry, I forgot to include that the backend is sql server (2012 in this case, but originally developed for v2005).

It would be neat to raise errors with messages from the backend - less checks to manage on the front end.

Thanks for any help with this!

-Matt G.
 

Minty

AWF VIP
Local time
Today, 17:48
Joined
Jul 26, 2013
Messages
10,355
How are the deletes being made ?

If it's via a form then you could do the delete by calling a stored procedure and return a message via that route.
 

madEG

Registered User.
Local time
Today, 13:48
Joined
Jan 26, 2007
Messages
309
Previously I've always done things in the VBA area on form's events, so triggers and SPs are new to me...

I was just looking at calling SPs from a form. This is interesting!

Thanks!
 

nfk

Registered User.
Local time
Today, 10:48
Joined
Sep 11, 2014
Messages
118
If you want to check the triggers on the database go to ssms, select the database, expand "Programmability", expand "Database triggers".

If you want to know what the hell is happening when the trigger happens or before/after then use your profiler and have a look.
 

Users who are viewing this thread

Top Bottom