Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to handle "No Checkout Authority could be found for PO" Error Message in SQL Server?

Posted on 2010-08-16
4
Medium Priority
?
257 Views
Last Modified: 2012-05-10
Dear, I have written a trigger, when user voilates trigger gives proper Error Message, but user need to close that PO and reopen it to make it correct. if user does not re-open the PO. he gets the error which is in attached file.
trigger code is given eblow.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [POSOProcessing]  
   ON  [dbo].[InvNum]
   AFTER UPDATE

AS
      SET NOCOUNT ON;
Begin
      If exists(select * from _btblInvoiceLines      Where iInvoiceID in ( Select AutoIndex From Inserted) and fUnitpriceincl >1000)
      Begin
            RAISERROR ('Enter Price for KG',16,1)
      End
End


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [POSOProcessing]  
   ON  [dbo].[InvNum] 
   AFTER UPDATE 
	
AS 
	SET NOCOUNT ON;

Begin
	If exists(select * from _btblInvoiceLines	Where iInvoiceID in ( Select AutoIndex From Inserted) and fUnitpriceincl >1000)
	Begin
		RAISERROR ('Enter Price for KG',16,1)
	End
End

Open in new window

Check-out-Authority.bmp
0
Comment
Question by:mahmood66
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 11

Assisted Solution

by:Larissa T
Larissa T earned 1500 total points
ID: 33445830
Not sure what it means "user need to close that PO and reopen it"
Even if user gets error message all updated on a table  will be already done
In general This type of validation is better to do in UI level.

here is test for your trigger:
create table mytable (c int)
go
create TRIGGER tr_mytable   ON  mytable    AFTER UPDATE
AS
Begin
            RAISERROR ('Enter Price for KG',16,1)
End
go
insert into mytable values(5)
go
select * from  mytable
update mytable set c=10
select * from  mytable
0
 

Author Comment

by:mahmood66
ID: 33461693
Dear, i cannot do at UI level, because the source code we are not having, that's why i am trying to ristrict using trigger.
"Even if user gets error message all updated on a table will be already done" ( your are right but when my statement RaisError executes it rollbacks the transaction so my database is not save the wrong data).
0
 
LVL 11

Accepted Solution

by:
Larissa T earned 1500 total points
ID: 33464811
execute all actions needed for
"user need to close that PO and reopen it"  
and then raise error
0
 

Author Closing Comment

by:mahmood66
ID: 33816853
with the help of experts idea, i have done.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question