SQL Server Performance Forum – Threads Archive

Need Help in Trigger

Hi, Plz chek out my trigger alter trigger opreceiptcancel on opreceipt instead of delete as
declare @opbillid varchar(30)
set @opbillid=(select receiptno from opreceipt where receiptno in
(select top 1 opbillid from canoptestorder order by canceldatetime desc)) if @opbillid>0 BEGIN
RAISERROR (‘Bill can not be Canceled – Transaction aborted.’, 16, 1)
ROLLBACK TRANSACTION
return
END
———-
problem is once the trigger fired a Error diolog box appear ,and couldn’t close the Dialog box..have to close the Application to close the Err dialog box. Plz let me know how can i sort it. Thnks Jeyanth

Which application? We use RAISERROR against a MS Access frontend with ODBC linked tables all the time, no problems whatsoever – but we use only AFTER/FOR triggers. Why are you using an INSTEAD OF trigger? By the way, no need to use a variable: ALTER TRIGGER opreceiptcancel ON opreceipt INSTEAD OF delete AS IF
(select receiptno from opreceipt where receiptno in
(select top 1 opbillid from canoptestorder order by canceldatetime desc))
>0
BEGIN
RAISERROR (‘Bill can not be Canceled – Transaction aborted.’, 16, 1)
ROLLBACK TRANSACTION
return
END Also, why are you not using the DELETED conceptual table? Your query is checking ALL rows in your opreceipt table.

Hi,
Thanks
Whats is that DELETED table. Jey

You’re using triggers, and you don’t know about INSERTED and DELETED? Start reading BOL!

…or search the fora here. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Think of both pseudotables as snapshots of the T-Log before and after the modification. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

Well, you are caught by Frank again. So please take your time to read Books online for complete understanding on your basic questions. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>