recieving trigger messages from SQL to the client

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

recieving trigger messages from SQL to the client

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-02-28 : 07:42:57

Phil writes "I've recently added some triggers to our database to prevent duplicate data from entering our system. I created nice messages and everything in the trigger. You can see these messages just fine when you are in SQL. How can I pass these messages up the client?

For instance, we have a web page that calls an SP. The SP has the possibility of setting of the trigger, if it try's to insert/update duplicate data. The web client only gets it's message from the SP, currently. How can I tell the web client that the trigger fired, do not deduct money from credit card? :)

Thanks,
Phil"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-02-28 : 07:44:31

If you're using PRINT to show messages, don't. Use RAISERROR. This generates an error message that you can capture using standard error handling in your code. There's more about RAISERROR in Books Online.

tacket
Starting Member

47 Posts

Posted - 2003-02-28 : 10:42:32

Ok,

I'm using RaisError in the trigger. It looks like this:

BEGIN
ROLLBACK TRANSACTION
RAISERROR('Trigger: People_UIN fired. Cannot insert duplicate UIN into people table. Operation cancelled.', 16, 1)
RETURN
END

I would like to capture this message in the SP and pass to the client. Unfortunately, when this trigger fires, the SP stops. How can I capture this message and pass it on to the client?

Thanks,
Phil

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-28 : 11:00:39

Cut to the chase...you're trying to prevent "duplicates" with a trigger?

Ummmm..how about a unique index? And trap the error with @@Error when a dup attempt is tried?

Just my opinion

Brett

8-)

tacket
Starting Member

47 Posts

Posted - 2003-02-28 : 14:31:46

The real reason I'm not using the 'unique index' is because the keys on all our tables are guids. The real key in this case is an 9 digit number, but the actual key is a 36-char field or whatever. I did some reading on the 'unique index' mainly because I just heard about them recently. It looks as though the index is a 'physical barrier' not a logical one. It checks the uniqueness after the insert, then does a rollback if the uniqueness fails.

We use SP's and have an error messaging system in place (not a good one in my opinion) from the SPS. Each SP returns back a code - usually a 1 if it worked or a negative number if it didn't. We then grab the message from that code on another SQL table.

My main question is there a way, mabey by changing the severity level of the Raiserror number, to allow the SP to continue on so that I can grab my own internally generated code. The trigger will be what prevents the data from being inserted. Like I said I can't use constraints because each record I have in this table will always be unique due to the fact we are using a guid.

Any ideas?
Thanks,
Phil

tacket
Starting Member

47 Posts

Posted - 2003-02-28 : 15:08:37

Here is the whole trigger:

CREATE TRIGGER People_UIN ON People FOR INSERT, UPDATE AS

IF EXISTS
(SELECT INS.UIN
FROM inserted INS
left join people PPL
on PPL.UIN = INS.UIN
where PPL.HID <> INS.HID)

BEGIN
ROLLBACK TRANSACTION
RAISERROR('Trigger: People_UIN fired. Cannot insert duplicate UIN into people table. Operation cancelled.', 16, 1)
RETURN
END

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-28 : 17:09:19

Well, Unique Indexes are not Primary keys btw, Your primary key of GUID is probably an identity column? Why? To create surrogate keys? Sounds like your Natural key is UIN. Why not use that as Primary? Also the trigger is independent of any s/p. That's why I would handle the errors in an sp, put a unique index on the column and handle the error like:

INSERT INTO TABLE (COL1, ect) SELECT COL1, ect FROM TABLE

SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error

If @Error_Out <> 0
BEGIN
Select @Error_Loc = 14
Select @Error_Type = 50001
GOTO Load_Ledger_Init_sp_Error
END

If @Rows = 0
BEGIN
SELECT @Error_Loc = 15
SELECT @Error_Message = 'Business error message. ie expected rows to load and got nothing'
SELECT @Error_Type = 50002
GOTO Load_Ledger_Init_sp_Error
END

Then have Labels like. This goes at the end of the spoc:

COMMIT TRAN

Load_Ledger_Init_sp_Exit:

SET NOCOUNT OFF
return @Comp_Code

Load_Ledger_Init_sp_Error:
Rollback TRAN

Select @Comp_Code = -1, @Comp_msg = @Error_Message, @Load_Ind = 'N'

If @Error_Type = 50001
BEGIN
Select @error_message = (Select 'Location: ' + RTRIM(Convert(char(3),@Error_Loc))
+ ' @@ERROR: ' + RTRIM(Convert(char(6),error))
+ ' Severity: ' + RTRIM(Convert(char(3),severity))
+ ' Message: ' + RTRIM(description)
From master..sysmessages
Where error = @error_out)
END
If @Error_Type = 50002
BEGIN
Select @Error_Message = 'Location: ' + RTRIM(Convert(char(3),@Error_Loc))
+ ' Severity: UserLevel '
+ ' Message: ' + RTRIM(@Error_Message)
END

RAISERROR @Error_Type @Error_Message

GOTO Load_Ledger_Init_sp_Exit

Anyway, That's what we do.

Brett

8-)

tacket
Starting Member

47 Posts

Posted - 2003-03-03 : 10:37:44

Thanks for the input. I like the way you use the master.sysmessages table to load/return your messages. That's really a good idea. Why don't we use the UIN as the key? Well the reason at first we didn't use it was because it was 'unstable'. Meaning somebody could change it. After awhile we got used to having GUID's as keys. I really really like having GUIDS as keys. It means one extra step in your SQL code, but they are worth having when you have more than one key that makes up the table. Anyway, off subject...

Because I'm not the SP writer for a lot of SP's that make it into production. The trigger was a safeguard against duplicate data when other people don't do all the stuff they should in the SP. My problem came when the trigger actually worked. Somebody who had duplicate records already kept trying to put money on their card and it kept taking the money away from their real credit card. The problem was the trigger was firing, but the SP had no clue it was. This lead to the person putting like $500 on their meal card and seeing no change in their balance. The reason was because the SP wasn't picking up that the trigger fired. They weren't communicating. I kind of like the idea of having a trigger fire to guarantee data integrity. The reason for this is you are guaranteed if the trigger is working not to get whatever bad data the trigger is preventing. Whether you do inserts through scripts or SP's or whatever. Not all of our data comes through SP's.

If I could just figure out how to keep an SP going when a trigger fires... :)

Try creating a simple SP on an insert or something like this:

CREATE PROCEDURE dbo.PeopleTest
@UIN varchar(16)

AS
SET NOCOUNT ON

DECLARE
@ReturnStatus int

insert into people
(UIN)
values
(@UIN)

select 'hi'

ReturnLogic:
SELECT @ReturnStatus AS ReturnStatus

RETURN

GO

With my current trigger in place (the one on the people table), if I insert the same UIN 2 times the trigger will fire the second time. This means I will never see the Select 'hi' line. That select 'hi' line represents my error checking etc. If the SP is never getting to that point the trigger is useless. I want the trigger to fire, (thus preventing duplicating data) while not exiting the SP.

Thanks,
Phil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources