Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
 Posted - 2002-10-08 : 13:37:25
|
| I've got a trigger that checks a lookup to make sure a valid ID is present: CREATE TRIGGER trInsUpdNewTable ON NewTable FOR INSERT, UPDATE AS IF (SELECT COUNT (*) FROM ID_lookup, inserted WHERE ID_lookup.ID = inserted.ID) = 0 BEGIN PRINT 'The ID you have entered is not listed in the central registry' PRINT 'Please check with Admin to resolve this problem' ROLLBACK TRANSACTION END Until my new web interface is ready, I'm using Microsoft Access link tables to allow a select group of users to write data to the SQL tables. Now, I know my "PRINT" message will not display in Access when the trigger executes, but I notice during testing that if I attempt to enter an invalid ID, there is no message at all from the ODBC-linked SQL table. The rollback simply executes and does what I want, but the user has no idea what has happened. I notice that when a SQL rule is violated via Access link table, an ODBC error will present. It's not pretty, but at least it's something. I don't want to get into writing special popups on the Access side. Is there any way to raise an ODBC-type error when my trigger has run? Thanks. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-08 : 14:12:17
|
quote:
Is there any way to raise an ODBC-type error when my trigger has run?
Yep, if you use the...wait for it...RAISERROR command!  CREATE TRIGGER trInsUpdNewTable ON NewTable FOR INSERT, UPDATE AS IF (SELECT COUNT (*) FROM ID_lookup, inserted WHERE ID_lookup.ID = inserted.ID) = 0 BEGIN RAISERROR('The ID you have entered is not listed in the central registry. Please check with Admin to resolve this problem.',16,1)ROLLBACK TRANSACTION RETURNENDBTW, unless you're debugging a procedure, you should not use PRINT in a trigger or stored procedure. Its results are neither rows/data sets nor error messages, and external applications cannot retrieve them.Edited by - robvolk on 10/08/2002 14:15:17 |
 |
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2002-10-08 : 14:29:51
|
| Rob, thanks. I guess I walked into that one... ;-) |
|
|
|
|
|
|
|
|
message to Access after trigger runs?