Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-13-2017, 12:04 PM   #1
madEG
Newly Registered User
 
Join Date: Jan 2007
Location: Washington D.C.
Posts: 304
Thanks: 32
Thanked 0 Times in 0 Posts
madEG is on a distinguished road
this Snowflake is "Triggered!"

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.
Attached Images
File Type: jpg New SQL Error.JPG (31.4 KB, 38 views)

madEG is offline   Reply With Quote
Old 09-14-2017, 06:12 AM   #2
madEG
Newly Registered User
 
Join Date: Jan 2007
Location: Washington D.C.
Posts: 304
Thanks: 32
Thanked 0 Times in 0 Posts
madEG is on a distinguished road
Re: this Snowflake is "Triggered!"

...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.
madEG is offline   Reply With Quote
Old 09-14-2017, 06:17 AM   #3
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,210
Thanks: 123
Thanked 1,421 Times in 1,393 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: this Snowflake is "Triggered!"

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.

__________________
If we have helped;
Please click the 'reputation' scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
madEG (09-14-2017)
Old 09-14-2017, 10:09 AM   #4
madEG
Newly Registered User
 
Join Date: Jan 2007
Location: Washington D.C.
Posts: 304
Thanks: 32
Thanked 0 Times in 0 Posts
madEG is on a distinguished road
Re: this Snowflake is "Triggered!"

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!
madEG is offline   Reply With Quote
Old 09-20-2017, 10:59 AM   #5
nfk
Newly Registered User
 
Join Date: Sep 2014
Location: All the way south.
Posts: 90
Thanks: 11
Thanked 3 Times in 3 Posts
nfk is an unknown quantity at this point
Re: this Snowflake is "Triggered!"

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.

__________________
Microsoft Access Hater...

Proud owner of 2 bans.
nfk is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Input " Like A* " in Validation Rule Property but it shows " ALike "A*" " AccessPractice Tables 2 06-24-2016 05:37 AM
If "TableA.FieldA" exists in "TableB.FieldB" then return "1" else "2" shdale Queries 8 10-29-2014 05:20 AM
Expr1: Format("012P","0000") Why I get "0001" amolin General 4 10-10-2012 08:33 PM
Query to Convert "Name" field to "Last Name" "First Name" "Middle Name"? derekbeck Queries 5 03-26-2012 03:23 PM




All times are GMT -8. The time now is 08:53 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World