September 27, 2004 at 6:07 pm
I have some business rules being enforced with a trigger. Iām updating the table from several stored procedures. I want to set the @@ERROR for the stored procedure in the trigger. Then pass back the return value of the stored procedure to the calling application in this case an Access .adp (ado&vba) for success or fail I do not want to make two passes at the data, once in the proc and again on the trigger. Here are the scripts for an example of what I would like to do.
CREATE TABLE [TrigProblem] (
[MyID] [int] IDENTITY (1, 1) NOT NULL ,
[MyText] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_TrigProblem] PRIMARY KEY CLUSTERED
( [MyID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
--populate table
INSERT INTO TrigProblem (MyText)
SELECT 'No Updates' AS MyText
INSERT INTO TrigProblem (MyText)
SELECT 'Anything Goes' AS MyText
CREATE TRIGGER TrigProblem_Trigger1
ON dbo.TrigProblem
FOR INSERT, UPDATE
AS
IF EXISTS (SELECT MyID FROM INSERTED WHERE (MyID = 1))
BEGIN
RAISERROR ('MyID of 1 cannot be updated' , 16,1) WITH SETERROR
ROLLBACK TRAN
END
CREATE PROCEDURE dbo.WorkBench
AS
UPDATE TrigProblem
SET MyText = 'My New Value'
WHERE MyID = 1)
RETURN @@ERROR
Declare @return_status int
EXEC @return_status = Workbench
SELECT @return_status
If anyone can give me some suggestions if this can be done and how to do it or a work around with making two passes I would appreciate it.
September 28, 2004 at 7:30 am
In my opinion the problem is given by the ROLLBACK TRANS statement that cause batch to stop immediately,( if you comment the statement you can see what you probably want). What I could suggest it's the use of transaction not in the trigger, but I think you already tried it.
If not already seen, however I can indicate the article "Rollbacks in Stored Procedures and Triggers" in the SQL Server BooksOnline.
At the moment I haven't well understood how to solve the problem of trigger and transaction in sql server also in my applications, however I see it's a rather common problem understanding the exact mechanisms.
Best regards
pietro munari
September 28, 2004 at 10:03 am
It is possible to create your own user-defined messages using sp_addmessage (c.f., BOL). The user-defined error numbers start at 50001. However, messages are not database specific. If moving the related database to a different server, the messages must also be moved (created). Also, make sure the error number range you use does not or will not overlap with current or future ranges used by other applications. Perhaps an offset can be used to permit you to change the range used if this is a concern.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. š
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply