Author |
Topic |
KingCarlos
Yak Posting Veteran
74 Posts |
Posted - 2013-06-17 : 02:58:12
|
Both RAISERROR and PRINT can be used to return informational or warning messages to an application. The message text returned by RAISERROR can be built using string substitution functionality similar to the printf_s function of the C standard library, whereas PRINT can only return a character string or character expression. A RAISERROR severity of 11 to 19 executed in the TRY block of a TRY…CATCH construct causes control to transfer to the associated CATCH block. Specify a severity of 10 or lower to return messages using RAISERROR without invoking a CATCH block. PRINT does not transfer control to a CATCH block.My question is I want to know how to add this as an error message to a business application such as an accounting system, is it a requirement for the application to have an API to allow this? |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-17 : 03:34:22
|
you need to add IF check based on your business requirement and call RAISERROR inside IF check block. Then control will get transfered to catch block if present where code can be written to return the error message to calling application. There's no need to separate API for that.Also keep in mind that if using SQL 2012 RAISERROR is deprecated and you should be using THROW instead inside TRY CATCH------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
KingCarlos
Yak Posting Veteran
74 Posts |
Posted - 2013-06-17 : 19:12:25
|
Thanks, the application I have is not mine and I do not have access to the source code. Basically the software has a feature missing that I can place at DB level but I need to tell the end user via a on screen message that this feature has occurred.I believe the RAISEERROR command cannot do this, is that correct? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-18 : 00:29:51
|
quote: Originally posted by KingCarlos Thanks, the application I have is not mine and I do not have access to the source code. Basically the software has a feature missing that I can place at DB level but I need to tell the end user via a on screen message that this feature has occurred.I believe the RAISEERROR command cannot do this, is that correct?
Nope it can do itBased on yur conditions you can write a custom error message and raise it through RAISERROR but obviouly you need access to db for that.If you cant even access db only solution would be to do the check and display message using your front end code------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
KingCarlos
Yak Posting Veteran
74 Posts |
Posted - 2013-06-18 : 02:20:25
|
OK, thanksSo I have created a table to warn the individual staff member, as followscreate table X_MYpeople (staffno int, spid int)GOThen a trigger create trigger X_GetSPID on staff for update asset nocount onif update(last_login) begin delete from X_MYpeople where spid = @@spid insert into X_MYpeople (staffno, spid) select i.staffno, @@spid from inserted iendGOThen the functioncreate function dbo.X_GetMYpeopleno () returns intas begin return (select top 1 staffno from X_MYpeople where spid = @@SPID)endThis trigger works with the rollback but no error is displayed on screenCreate TRIGGER [dbo].[X_PREVENT_ALLOCATION]ON [dbo].[ALLOCATIONS]FOR INSERT, UPDATEASBEGINSET NOCOUNT ONBEGINIF DBO.X_GETMYPEOPLENO() IN (1,8)BEGINRAISERROR ('YOU ARE NOT ALLOWED TO ALLOCATE PAYMENTS',16,1) ROLLBACK TRANENDEND ENDAny advice on what I am missing |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-18 : 02:26:33
|
sorry i didnt get what you're trying to implement here. whats the deal with @@SPID?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
KingCarlos
Yak Posting Veteran
74 Posts |
Posted - 2013-06-18 : 02:29:58
|
I have listed the individuals that should not perform a task and when they do it (because there is no inbuilt system feature to block) I want the transaction to be rolled back and then a warning sent to the end user on screen to seek an administrator to perform the task. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-18 : 02:32:03
|
quote: Originally posted by KingCarlos I have listed the individuals that should not perform a task and when they do it (because there is no inbuilt system feature to block) I want the transaction to be rolled back and then a warning sent to the end user on screen to seek an administrator to perform the task.
How can you identify individuals by means of @@SPID? it will just return you current processid.why not use SUSER_SNAME() or similar functions to get logged in user and see if they're member of unauthorised group?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
KingCarlos
Yak Posting Veteran
74 Posts |
Posted - 2013-06-21 : 01:14:19
|
quote: Originally posted by visakh16
quote: Originally posted by KingCarlos I have listed the individuals that should not perform a task and when they do it (because there is no inbuilt system feature to block) I want the transaction to be rolled back and then a warning sent to the end user on screen to seek an administrator to perform the task.
How can you identify individuals by means of @@SPID? it will just return you current processid.why not use SUSER_SNAME() or similar functions to get logged in user and see if they're member of unauthorised group?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Cheers for that, I have tried using the SUSER_SNAME() but cannot get it to Raise an error. Can you provide a simple example on how I can capture the SQL logon and raise an error to that user that they cannot change data. thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-21 : 02:04:30
|
Ok...tell me under what condition you need to raise the error? do you've a table where you store which users should have access to what etc details?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
KingCarlos
Yak Posting Veteran
74 Posts |
Posted - 2013-06-23 : 19:39:34
|
Thanks, so lets say they enter data into a table I want to rollback that transaction and then raise an error to the end user that the record was not accepted. I will have a table of SQL authentication users that I can call upon that will be used as the criteria that they cannot enter the record.thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-24 : 00:46:09
|
ok. in that case you need a check like this..IF EXISTS(SELECT 1 FROM YourUserTable WHERE user_name = SUSER_SNAME() AND Access_Right = 'Restricetd') RAISERROR 'The logged in user doesnt have access to perform the current operation',16,1ELSE ... actual logic here I've assumed column names so make sure you use correct columnnames instead. I also assume you've a column which gives details on user access in table like granted, restricted etcIf your table structure is different please post it otherwise we cant guess on it------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
KingCarlos
Yak Posting Veteran
74 Posts |
Posted - 2013-06-24 : 01:25:57
|
thanks for that, unfortunately hit a snag,here is my table of users create table X_Staffs (staffno int, enduser nvarchar(256), access_right varchar(50), task varchar(50))GOI have added into my table a end userinsert into X_staffs (staffno, enduser, access_right, task)values ('1','sa','Restricetd','allocation')but this trigger is not working.Create TRIGGER [dbo].[X_PREVENT_ALLOCATION1]ON [dbo].[DR_ALLOCATIONS]FOR INSERT, UPDATEASBEGINSET NOCOUNT ONBEGINIF (SELECT 1 FROM X_Staffs WHERE enduser = SUSER_SNAME() AND Access_Right = 'Restricetd') RAISERROR 'The logged in user doesnt have access to perform the current operation',16,1ROLLBACK TRANENDEND ENDI would hope it would capture the end user doing an activity, cross reference the table holding their details, if restricted roll back the transaction and then raise an error message. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-24 : 01:31:06
|
i cant see any condition in your trigger code where you're checking for task details. The way its written currently it will throw error for any activity by the user on the table DR_ALLOCATIONSI think it should be likeCreate TRIGGER [dbo].[X_PREVENT_ALLOCATION1]ON [dbo].[DR_ALLOCATIONS]FOR INSERT, UPDATEASBEGINSET NOCOUNT ONBEGINIF (SELECT 1 FROM X_Staffs WHERE enduser = SUSER_SNAME() AND Access_Right = 'Restricetd' AND task = yourPassedTaskValue)RAISERROR 'The logged in user doesnt have access to perform the current operation',16,1ROLLBACK TRANENDEND END You can pass the task value inside or just use a control table to capture and pass it inside triggerAlso keep in mind that whatever you're implementing in this trigger will get executed only based on INSERT and UPDATE activity on DR_ALLOCATIONS table.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
KingCarlos
Yak Posting Veteran
74 Posts |
Posted - 2013-06-24 : 02:15:41
|
I think there is something fundamentally wrong in the code, this is the error when trying to create the trigge.rMsg 4145, Level 15, State 1, Procedure X_PREVENT_ALLOCATION1, Line 9An expression of non-boolean type specified in a context where a condition is expected, near 'RAISERROR'.Msg 102, Level 15, State 1, Procedure X_PREVENT_ALLOCATION1, Line 13Incorrect syntax near 'END'. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-24 : 02:20:04
|
You missed an EXISTS condition in your posted code and I just carried it all along as I copied from it Create TRIGGER [dbo].[X_PREVENT_ALLOCATION1]ON [dbo].[DR_ALLOCATIONS]FOR INSERT, UPDATEASBEGINSET NOCOUNT ONIF EXISTS(SELECT 1 FROM X_Staffs WHERE enduser = SUSER_SNAME() AND Access_Right = 'Restricetd' AND task = yourPassedTaskValue)BEGINRAISERROR 'The logged in user doesnt have access to perform the current operation',16,1ROLLBACK TRANEND END ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
KingCarlos
Yak Posting Veteran
74 Posts |
Posted - 2013-06-24 : 02:30:54
|
there seems to be a problem on the RAISEERROR command line, see belowMsg 102, Level 15, State 1, Procedure X_PREVENT_ALLOCATION1, Line 9Incorrect syntax near 'The logged in user doesnt have access to perform the current operation'. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-24 : 02:35:27
|
[code]Create TRIGGER [dbo].[X_PREVENT_ALLOCATION1]ON [dbo].[DR_ALLOCATIONS]FOR INSERT, UPDATEASBEGINSET NOCOUNT ONIF EXISTS(SELECT 1 FROM X_Staffs WHERE enduser = SUSER_SNAME() AND Access_Right = 'Restricetd' AND task = yourPassedTaskValue)BEGINRAISERROR (N'The logged in user doesnt have access to perform the current operation',16,1)ROLLBACK TRANEND END[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
KingCarlos
Yak Posting Veteran
74 Posts |
Posted - 2013-06-24 : 02:47:02
|
thanks, still the raise error does not give an error when performing an action in the application.If you can give me an example fo a raiseerror command when a record is deleted I will test that but I cannot see the RAISEERROR working, happy to be proven wrong. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-24 : 02:49:13
|
If you can give me an example fo a raiseerror command when a record is deletedthats not the case we're dealing hereas trigger you've written is only for INSERT and UPDATE actions------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
KingCarlos
Yak Posting Veteran
74 Posts |
Posted - 2013-06-24 : 06:49:52
|
Does it matter? I can write simple triggers based on INSERT, UPDATE and DELETE. The question is can it raise an error on screen to the end user when they perform one of these functions in an application.so lets take an UPDATE, a user adds a record and the application has no prompt to tell the user to add Data for ABC. My original question was can the SQL RAISEERROR inform the user on screen that this is required. My suspicion is no it cannot, you claim it can, I am yet to see my suspicion proved wrong.If you can show me the way it would be appreciated based on an application which uses SQL authentication for all users.thanks |
|
|
Previous Page&nsp;
Next Page
|