Thread: how to create rule as on delete

This is my rule for doing the delete event. The rule
can be created. But the problem is, after the record
has been deleted from the 'mytable', this record
cannot be inserted into table 'maytable_log'.

CREATE RULE on_delete AS ON DELETE TO mytable DO
INSERT INTO mytable_log values (old.id,old.name);                


If I add the DO INSTEAD,the record can be inserted
into 'maytable_log' and also still remain in the table
'maytable'. Which is exactly not exist at all. It just
show it as im using the DO INSTEAD.

CREATE RULE on_delete AS ON DELETE TO mytable DO
INSTEAD INSERT INTO mytable_log values
(old.id,old.name); 

My problem is, how to insert the deleted record into
table 'mytable_log' without showing it in table
'maytable'. I really need the solution..please
                                  

    
__________________________________ 
Yahoo! Mail - PC Magazine Editors' Choice 2005 
http://mail.yahoo.com

> If I add the DO INSTEAD,the record can be inserted
> into 'maytable_log' and also still remain in the table
> 'maytable'. Which is exactly not exist at all. It just
> show it as im using the DO INSTEAD.
>
> My problem is, how to insert the deleted record into
> table 'mytable_log' without showing it in table
> 'maytable'. I really need the solution..please

Add a DELETE clause on the table 'maytable' as part of the DO INSTEAD,
after the INSERT INTO.

Or, IIRC, you could INSERT INTO (without the DO INSTEAD) with the
variable NEW - the NEW variable will store the row being deleted.

Best regards,

Ben Stewart

--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services, Student Software Engineer (RBAU/EQS4)
Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA
Tel: +61 3 9541-7002 Fax: +61 3 9541-7700
mailto:ben.stewart@au.bosch.com
http://www.bosch.com.au/

I think you want a delete trigger which does your
insert and then follows through with the delete
by returning old.

--elein
elein@varlena.com

On Tue, Oct 18, 2005 at 09:43:34PM -0700, efa din wrote:
> This is my rule for doing the delete event. The rule
> can be created. But the problem is, after the record
> has been deleted from the 'mytable', this record
> cannot be inserted into table 'maytable_log'.
> 
> CREATE RULE on_delete AS ON DELETE TO mytable DO
> INSERT INTO mytable_log values (old.id,old.name);     
>             
> 
> 
> If I add the DO INSTEAD,the record can be inserted
> into 'maytable_log' and also still remain in the table
> 'maytable'. Which is exactly not exist at all. It just
> show it as im using the DO INSTEAD.
> 
> CREATE RULE on_delete AS ON DELETE TO mytable DO
> INSTEAD INSERT INTO mytable_log values
> (old.id,old.name); 
> 
> My problem is, how to insert the deleted record into
> table 'mytable_log' without showing it in table
> 'maytable'. I really need the solution..please
> 
>                                    
> 
> 
>     
>         
> __________________________________ 
> Yahoo! Mail - PC Magazine Editors' Choice 2005 
> http://mail.yahoo.com
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>