Actually I will apologize first, I jumped the gun on stating what I thought
the problem was. It does have to do with this view but I believe it has
more to do with another topic I posted a little while ago about using a
stored procedure within a view. So sorry for changing midstream but I will
still post the code I've been testing with. Like I said as straight SQL
inside the delete trigger everything works as expected, but when used as the
view version I get a big crash.



This is the view I am using in my delete trigger



CREATE VIEW VW_MAILINGRUN(

SUBSCRIBER_NO,

BUYERNAME,

FIRSTNAME,

LASTNAME,

COMPANY,

EMAIL,

CURRENCY,

COUNTRYNAME,

POSTAL,

CITY,

REGIONNAME,

ADDRESS1,

ADDRESS2,

ISSUEDESCRIPTION,

RUNDATE,

MAILINGRUN_NO,

EXPIRYISSUE_NO,

COPIES)

AS

SELECT B.BUYER_NO, B.BUYERNAME, B.FIRSTNAME, B.LASTNAME,

B.COMPANY, B.EMAIL, B.CURRENCY, CN.COUNTRYNAME, AD.POSTAL, AD.CITY,

RN.REGIONNAME, AD.ADDRESS1, AD.ADDRESS2, I.ISSUEDESCRIPTION, IM.MAILINGDATE,

IM.MAILINGRUN_NO, IM.EXPIRYISSUE_NO, IM.COPIES

FROM BUYER B, ISSUE I, ISSUESMAILED IM, ADDRESS AD, COUNTRY CN, REGION RN

WHERE IM.BUYER_NO = B.BUYER_NO

AND IM.ISSUE_NO = I.ISSUE_NO

AND B.BUYER_NO = AD.BUYER_NO

AND AD.ADDRESS_NO = (SELECT NEW_ADDRESS_NO

FROM SP_GETCURRENTADDRESS(B.BUYER_NO, 1, 'NOW'))

AND AD.COUNTRY_NO = CN.COUNTRY_NO

AND AD.REGION_NO = RN.REGION_NO

AND IM.MAILINGSTATUS = 2

;



The weird part in this code is where I am joining the address table to the
stored procedure

AND AD.ADDRESS_NO = (SELECT NEW_ADDRESS_NO

FROM SP_GETADDRESS(B.BUYER_NO, 1, 'NOW'))



This stored proc just takes the 3 params you see, a Buyer_no, an AddressCode
(Billing = 0, Shipping = 1), and a date. It will return an address key
corresponding to all three. I try to guarantee an address key being
returned inside this stored proc. and have an exception raised if all my
attempts in the stored proc. fail.



I am not sure if I am just complicating things too much and I've decided to
change this way of getting an address so that I am not using a stored
procedure but maybe another view that can give me the same results. My
question I guess would be is this blatant abuse of SQL? Am I trying to do
something that I'm not capable of here? When I wrote it originally it made
sense to me ie) the B.BUYER_NO being able to be sent in as a parameter for
the stored proc. but now many months later and getting more used to Firebird
I know of several different and less complicated ways to get the same
result. Tia, Todd.



Also this is the code for my delete trigger that uses this View/SQL

CREATE TRIGGER BEFORE_MAILINGRUN_DELETE FOR MAILINGRUN

ACTIVE BEFORE DELETE POSITION 0

AS

DECLARE VARIABLE LMAILINGRUN_NO INTEGER;

DECLARE VARIABLE LSALE_NO INTEGER;

DECLARE VARIABLE LISSUE_NO INTEGER;

DECLARE VARIABLE LPUBLICATION_NO INTEGER;

DECLARE VARIABLE LISSUECOUNT INTEGER;

DECLARE VARIABLE LSTATUS INTEGER;

BEGIN

LMAILINGRUN_NO = OLD.MAILINGRUN_NO;



SELECT ISSUE_NO

FROM ISSUESMAILED

WHERE MAILINGRUN_NO = :LMAILINGRUN_NO

INTO :LISSUE_NO;



/* *********************************************** */

/* HERE IS THE INTERCHANGEABLE VIEW/SQL PART */

SELECT SUM(COPIES)

FROM VW_MAILINGRUN

WHERE MAILINGRUN_NO = :LMAILINGRUN_NO

INTO :LISSUECOUNT;



IF (LISSUECOUNT IS NULL) THEN
BEGIN

LISSUECOUNT = 0;

END



SELECT PUBLICATION_NO

FROM ISSUE

WHERE ISSUE_NO = :LISSUE_NO

INTO :LPUBLICATION_NO;



UPDATE ISSUE

SET QTYAVAILABLE = QTYAVAILABLE + :LISSUECOUNT

WHERE ISSUE_NO = :LISSUE_NO;



FOR

SELECT SALE_NO, MAILINGSTATUS

FROM ISSUESMAILED

WHERE MAILINGRUN_NO = :LMAILINGRUN_NO

INTO :LSALE_NO, :LSTATUS

DO

BEGIN

IF (LSTATUS = 2) THEN

BEGIN

UPDATE SALEPUBLICATION

SET QTYREMAINING = QTYREMAINING + 1

WHERE SALE_NO = :LSALE_NO

AND PUBLICATION_NO = :LPUBLICATION_NO;

END

END

END

_____

From: Daniel Rail [mailto:daniel@...]
Sent: Monday, September 13, 2004 12:52 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] View Crashing Trigger



Hi,

At September 13, 2004, 15:02, todd_pinel wrote:

> This is the first time I believe I have tried to use a view in a
> trigger and I can get around it no problem, although I am just
> wondering if anyone knows or can explain why this is happening for
> me? tia, Todd.

Can you show us what the view and the trigger looks like? It's hard
to give any advice without these details.

--
Best regards,
Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)






Yahoo! Groups Sponsor



ADVERTISEMENT

<http://us.ard.yahoo.com/SIG=129momjgh/M=295196.4901138.6071305.3001176/D=gr
oups/S=1705115386:HM/EXP=1095187964/A=2128215/R=0/SIG=10se96mf6/*http:/compa
nion.yahoo.com> click here



<http://us.adserver.yahoo.com/l?M=295196.4901138.6071305.3001176/D=groups/S=
:HM/A=2128215/rand=477393967>



_____

Yahoo! Groups Links

* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>

* Your use of Yahoo! Groups is subject to the Yahoo!
<http://docs.yahoo.com/info/terms/> Terms of Service.



[Non-text portions of this message have been removed]