Mssql Trigger Throw Error
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business sql server trigger error handling Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation
Sql Server Trigger Raiserror
Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like sql trigger try catch you, helping each other. Join them; it only takes a minute: Sign up Commit and Raiserror in a trigger up vote 0 down vote favorite Maybe there is a better way to do this. The jist of raiserror in trigger sql server 2008 I want is to have SQL Server raise me 2 types of errors: a WARNING and an ERROR from a trigger when I update a table. If SQL server returns a WARNING the trigger should COMMIT but show the warning to the user (using .NET - preferably through a SQL Exception which is only raised if severity > 10) and if it is an ERROR the trigger should ROLLBACK and show the ERROR to the
T-sql Throw
user (through a SQL Exception). My attempts (needless to say this isn't working) at this was to have a trigger like this: ALTER TRIGGER [dbo].[TR_TRANSACTION_UPDATE] ON [dbo].[tTRANSACTION] FOR UPDATE AS BEGIN ... BEGIN TRY DECLARE @id INT ,@maxid INT SELECT @id = 0 ,@maxid = MAX(transID) FROM INSERTED WHILE @id < @maxid BEGIN SELECT @id = MIN([TransID]) FROM INSERTED WHERE [TransID] > @id EXEC dbo.sp_CheckTransaction @TransID = @id END COMMIT TRANSACTION END TRY BEGIN CATCH DECLARE @err_msg VARCHAR(MAX), @err_sev AS INT, @err_state AS INT SELECT @err_msg = ERROR_MESSAGE(), @err_sev = ERROR_SEVERITY(), @err_state = ERROR_STATE() IF @err_state <> 120 -- '120 is not a fatal error from STORED_PROC ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION RAISERROR(@err_msg, @err_sev, @err_state) END CATCH END This trigger doesn't work because it thinks the transaction is uncommittable. Also, sp_CheckTransaction can RAISE 2 types of errors: RAISERROR(@msg, 15, 120) -- warning or RAISERROR(@msg, 15, 121) -- error The reason I can't use a severity < 15 is because I want a warning to show up in .NET (I can decide based on state & severity if it is a warning or an error). .NET SqlException is raised only for severity > 10. c# .net sql sql-server vb.net share|improve this question asked Mar 17 '11 at 17:23 Denis 3,85364076 add a comment| 3 Answers 3 active oldest votes up vote 3 down vote accepte
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this sql server instead of update trigger site About Us Learn more about Stack Overflow the company Business Learn more create trigger about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Trigger with a http://stackoverflow.com/questions/5342769/commit-and-raiserror-in-a-trigger RAISERROR and ELSE case issue up vote 1 down vote favorite I am trying to make a bit of code that takes in 2 separate columns, a month and a year. From there I want it to see if those numbers entered have already passed or not. If they have passed, cause an error to pass and stop the transaction. Otherwise, I want it http://stackoverflow.com/questions/28140138/trigger-with-a-raiserror-and-else-case-issue to continue on and insert new information into the table. I know I am close on getting this to work, but I cant seem to get the RAISERROR to fire. I am sure it has to do with the fact I am pretty new at this and I am missing some small detail. Currently I am taking the two months in as variables and the making a third variable to use to turn the other two into a proper datetime format. Then I use the datediff function to try and see if it has passed that way. To no avail though. I keep getting the insert function going, even if the card date is old. USE AdventureWorks2012 GO CREATE TRIGGER BadCreditCardDate ON Sales.CreditCard INSTEAD OF INSERT AS Begin DECLARE @ExpMonth tinyint, @ExpYear smallint, @ExpMonthYear datetime SELECT @ExpMonth=ExpMonth, @ExpYear=ExpYear, @ExpMonthYear = @ExpYear + '-' + @ExpMonth + '-00' FROM INSERTED IF DATEDIFF(MONTH,@ExpMonthYear,GETDATE()) < 0 BEGIN RAISERROR ('The Credit Card you have entered has expired.' ,10,1) ROLLBACK TRANSACTION END ELSE Begin INSERT INTO CreditCard (CardType, CardNumber, ExpMonth, ExpYear, ModifiedDate) Select CardType, CardNumber, ExpMonth, ExpYear, ModifiedDate FROM inserted END End sql-server triggers datediff raiserror share|impro
up Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's http://www.sqlservercentral.com/Forums/Topic1499938-3077-1.aspx On Home » SQL Server 2012 » SQL Server 2012 - T-SQL » Trigger with RAISERROR Trigger with RAISERROR Rate Topic Display Mode Topic Options Author Message PiManĂ©PiManĂ© Posted Monday, September 30, 2013 5:37 AM Mr or Mrs. 500 Group: General Forum Members Last Login: Yesterday @ 1:42 AM Points: 577, Visits: 1,299 Hi,Why doesn't a RAISERROR inside sql server an AFTER trigger "break" the transaction? Is there any flag or SET option to do it?I created a table with a trigger that just has RAISERROR and the UPDATE is executed any way...CREATE TABLE test (ID INT NOT NULL, Val INT);GOINSERT INTO test VALUES (1,1), (2,2), (3,3);GOCREATE TRIGGER trg_test_upd ON test AFTER UPDATEAS RAISERROR('Error', 16, 1)GOIf I execute:SELECT * sql server trigger FROM testBEGIN TRANUPDATE test SET val = 3 WHERE id = 1COMMIT TRANSELECT * FROM testThe update is committed even with the error raised from the trigger.The only way it isn't committed is if I have error handling:SELECT * FROM testBEGIN TRANBEGIN TRY UPDATE test SET val = 3 WHERE id = 1 COMMIT TRANEND TRYBEGIN CATCH ROLLBACK TRANEND CATCHSELECT * FROM test If you need to work better, try working less... Post #1499938 Sean PearceSean Pearce Posted Monday, September 30, 2013 6:35 AM Ten Centuries Group: General Forum Members Last Login: Monday, October 3, 2016 5:19 AM Points: 1,144, Visits: 3,432 You need to use ROLLBACK inside the trigger.CREATE TRIGGER trg_test_upd ON test AFTER UPDATEAS RAISERROR('Error', 16, 1); ROLLBACK;GO The SQL Guy @ blogspot@SeanPearceSQLAbout Me Post #1499961 Sean PearceSean Pearce Posted Monday, September 30, 2013 6:37 AM Ten Centuries Group: General Forum Members Last Login: Monday, October 3, 2016 5:19 AM Points: 1,144, Visits: 3,432 The behavior is the same with a stored procCREATE TABLE test (ID INT NOT NULL, Val I