db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik" <dag.wan...@oracle.com>
Subject Re: REMOVE
Date Thu, 12 Jun 2014 15:38:55 GMT
You need to send a mail from the account you subscribed with to

derby-user-unsubscribe@db.apache.org 
<mailto:derby-user-unsubscribe@db.apache.org>

cd this web page: http://db.apache.org/derby/derby_mail.html

Thanks,
Dag

On 12. juni 2014 17:24, Frank Rivera wrote:
>
> Sincerely,
>
> Frank Rivera
> LogicBit Software - Research Triangle Park, North Carolina
>
> Product Brochures http://houdiniesq.com/HoudiniEsq_2014BrochureProduct.pdf
> Product Testimonialshttp://houdiniesq.com/HoudiniEsq_2014BrochureShow.pdf
>
>   Support  919.238.7024Corporate  408.213.8138Sales  888.366.2280
>
> ***HoudiniEsq ®Legal Practice Management Suite*http://HoudiniEsq.com 
> <http://houdiniesq.com/>
>
>   Feature Rich web-based Legal Practice Management for today's cutting 
> edge Law Firms
>   Any Browser, Any OS, Anytime, Anywhere, Any size organization, SaaS 
> or On-premise
>
> Simple & Simplicity are not the same thing. *"Simple", as it turns 
> out, simply isn't good enough.*
> -----------------------------------------------------------------------------------------------------------------
> *NOTICE*: The sender does not accept liability for any errors 
> or omissions in the contents of this message which arise as
> a result of e-mail transmission.  This e-mail and any files 
> transmitted with it are confidential and are intended solely for
> the use of the individual or entity to which they are addressed. It is 
> not an offer or acceptance, and it is not intended to
> be all or part of an agreement.  This communication may 
> contain material protected by the attorney-client privilege.
> Original material created by the author may be protected by US 
> Copyright law, 17 U.S.C. § 101 et seq. This communication
> constitutes an electronic communication within the meaning of the 
> Electronic Communications Privacy Act, 18 U.S.C.
> § 2510, and its disclosure is strictly limited to the recipient 
> intended by the sender of this message.   If you are not the
> intended recipient or the person responsible for delivering the e-mail 
> to the intended recipient, be advised that you have
> received this e-mail in error and that any review, use, dissemination, 
> forwarding, printing, or copying of this e-mail is strictly
> prohibited. If you have received this e-mail in error, please notify 
> the sender immediately by return e-mail and delete this email.
>
> On Jun 12, 2014, at 11:05 AM, Rick Hillegas <rick.hillegas@oracle.com 
> <mailto:rick.hillegas@oracle.com>> wrote:
>
>> On 6/12/14 5:33 AM, Tim Dudgeon wrote:
>>> OK, so here's a simplified example. Didn't want to raise a JIRA as 
>>> I'm really not sure this is a bug.
>>>
>>> Tim
>>>
>>> drop table MAIN;
>>> drop table DELETIONS;
>>> drop table CHANGED;
>>>
>>> create table MAIN (
>>>    MAIN_ID INT generated always as identity,
>>>    GROUPING_ID INT,
>>>    NAME VARCHAR(50)
>>> );
>>>
>>> create table DELETIONS (
>>>    DELETIONS_ID int generated always as identity,
>>>    MAIN_ID INT,
>>>    NAME VARCHAR(50),
>>>    WHENITHAPPENED TIMESTAMP
>>> );
>>>
>>> create table CHANGED (
>>>    CHANGED_ID INT generated always as identity,
>>>    MAIN_ID INT,
>>>    NAME VARCHAR(50),
>>>    WHENITHAPPENED TIMESTAMP
>>> );
>>>
>>> insert into MAIN (GROUPING_ID, NAME) values
>>> (1, 'banana'),
>>> (1, 'lemon'),
>>> (1, 'grapefruit'),
>>> (2, 'apple'),
>>> (2, 'broccoli'),
>>> (3, 'strawberry'),
>>> (3, 'redcurrent'),
>>> (3, 'cherry');
>>>
>>> select * from MAIN;
>>>
>>> -- this trigger records what has been deleted from MAIN
>>> CREATE TRIGGER trg_delete1 AFTER DELETE ON MAIN
>>> REFERENCING OLD TABLE AS OLD FOR EACH STATEMENT MODE DB2SQL
>>> INSERT into CHANGED (MAIN_ID, NAME, WHENITHAPPENED)
>>> SELECT MAIN_ID, NAME, CURRENT_TIMESTAMP FROM OLD;
>>>
>>> -- tries to record all rows with the same GROUPING_ID as any of the 
>>> deleted rows
>>> CREATE TRIGGER trg_delete2 AFTER DELETE ON MAIN
>>> REFERENCING OLD TABLE AS OLD FOR EACH STATEMENT MODE DB2SQL
>>> INSERT into DELETIONS (MAIN_ID, NAME, WHENITHAPPENED)
>>> SELECT MAIN.MAIN_ID, MAIN.NAME, CURRENT_TIMESTAMP FROM MAIN, OLD
>>> WHERE MAIN.GROUPING_ID = OLD.GROUPING_ID;
>>>
>>> delete from MAIN where MAIN_ID = 1;
>>> select * from MAIN; -- banana deleted as expected
>>> select * from CHANGED; -- banana present as expected
>>> select * from DELETIONS; -- lemon and grapefruit present as 
>>> expected, but no banana
>> Hi Tim,
>>
>> This result looks correct to me. The trg_delete2 trigger fires AFTER 
>> the deletion and inside the same transaction. So it sees the state of 
>> the MAIN table AFTER the banana tuple was deleted. From its 
>> perspective, the banana table should not appear in the join.
>>
>> Or did I misunderstand your question?
>>
>> Hope this helps,
>> -Rick
>>>
>>>
>>>
>>>
>>>
>>>
>>
>


Mime
View raw message