I just created a Instead After Trigger whose syntax is given below:
Create trigger tgrInsteadTrigger on copytableto
Instead of Insert as
Declare @store_name varchar(30);
declare @sales int;
declare @date datetime;
select @store_name = i.store_name from inserted i
select @sales = i.sales from inserted i
select @date = i.Date from inserted i
begin
if (@sales > 1000)
begin
RAISERROR('Cannot Insert where salary > 1000',16,1); ROLLBACK;
end
else
begin
insert into copytablefrom(store_name, sales, date) values (@store_name, @sales, @date);
Print 'Instead After Trigger Executed';
end
End
In the above syntax I have used RAISERROR('Cannot Insert where salary > 1000',16,1)
But when I write RAISERROR('Cannot Insert where salary > 1000')
it gives the error "Incorrect syntax near ')'" on the same line.
Can anyone please explain the use of (16,1) here.
This question is related to
sql
sql-server
database
sql-server-2008
sql-server-2008-r2
16 is severity and 1 is state, more specifically following example might give you more detail on syntax and usage:
BEGIN TRY
-- RAISERROR with severity 11-19 will cause execution to
-- jump to the CATCH block.
RAISERROR ('Error raised in TRY block.', -- Message text.
16, -- Severity.
1 -- State.
);
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
You can follow and try out more examples from http://msdn.microsoft.com/en-us/library/ms178592.aspx
The answer posted to this question as an example taken from Microsoft's MSDN is nice however it doesn't directly demonstrate where the error comes from if it doesn't come from the TRY Block. I prefer this example with a very minor update to the RAISERROR Message within the CATCH Block stating that the error is from the CATCH Block. I demonstrate this in the gif as well.
BEGIN TRY
/* RAISERROR with severity 11-19 will cause execution
| to jump to the CATCH block.
*/
RAISERROR ('Error raised in TRY block.', -- Message text.
5, -- Severity. /* Severity Levels Less Than 11 do not jump to the CATCH block */
1 -- State.
);
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
/* Use RAISERROR inside the CATCH block to return error
| information about the original error that caused
| execution to jump to the CATCH block
*/
RAISERROR ('Caught Error in Catch', --@ErrorMessage, /* Message text */
@ErrorSeverity, /* Severity */
@ErrorState /* State */
);
END CATCH;
according to MSDN
RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]
16
would be the severity.
1
would be the state.
The error you get is because you have not properly supplied the required parameters for the RAISEERROR
function.
The severity level 16 in your example code is typically used for user-defined (user-detected) errors. The SQL Server DBMS itself emits severity levels (and error messages) for problems it detects, both more severe (higher numbers) and less so (lower numbers).
The state should be an integer between 0 and 255 (negative values will give an error), but the choice is basically the programmer's. It is useful to put different state values if the same error message for user-defined error will be raised in different locations, e.g. if the debugging/troubleshooting of problems will be assisted by having an extra indication of where the error occurred.
Source: Stackoverflow.com