Thread: Question

Question

From
Andriy Pyrozhenko
Date:
Hello pgsql-sql,
 I have the rule for table CREATE RULE del_jobs_del_activity AS ON DELETE TO jobs DO DELETE FROM activities WHERE
job_id= OLD.job_id;
 
 When i call: DELETE FROM jobs WHERE job_id IN (SELECT DISTINCT job_id FROM activities WHERE load_no = OLD.load_no)
Thisquery does not delete the record from jobs but delete the record by rule.
 

Best regards,Andriy                          mailto:andriy.pyrozhenko@vanjaonline.com





---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


Re: Question

From
"Josh Berkus"
Date:
Andriy,

>   I have the rule for table
>   CREATE RULE del_jobs_del_activity AS ON DELETE TO jobs DO DELETE
> FROM activities WHERE job_id = OLD.job_id;
> 
>   When i call:
>   DELETE FROM jobs WHERE job_id IN (SELECT DISTINCT job_id FROM
> activities WHERE load_no = OLD.load_no)
>   This query does not delete the record from jobs but delete the
>   record by rule.

And this is a problem because ... ?

-Josh


______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 


Re: Question

From
"Christopher Kings-Lynne"
Date:
Question: Why don't you just set up a foreign key that will achieve exactly
the same thing without using RULEs?

ie. ALTER TABLE activities ADD FOREIGN KEY (job_id) REFERENCES jobs(job_id)
ON DELETE CASCADE;

Chris

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Andriy Pyrozhenko
> Sent: Friday, 16 November 2001 5:39 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Question
>
>
> Hello pgsql-sql,
>
>   I have the rule for table
>   CREATE RULE del_jobs_del_activity AS ON DELETE TO jobs DO
> DELETE FROM activities WHERE job_id = OLD.job_id;
>
>   When i call:
>   DELETE FROM jobs WHERE job_id IN (SELECT DISTINCT job_id FROM
> activities WHERE load_no = OLD.load_no)
>   This query does not delete the record from jobs but delete the
>   record by rule.
>
> Best regards,
>  Andriy                          mailto:andriy.pyrozhenko@vanjaonline.com
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>



Re: Question

From
Andriy Pyrozhenko
Date:
Hello Christopher,
 I don't need delete record from jobs when I delete record from activity. But I need delete record from activity when I
deletethe record from jobs and I need to find this job_id in activities table! What must I do?
 

CKL> Question: Why don't you just set up a foreign key that will achieve exactly
CKL> the same thing without using RULEs?

CKL> ie. ALTER TABLE activities ADD FOREIGN KEY (job_id) REFERENCES jobs(job_id)
CKL> ON DELETE CASCADE;

CKL> Chris

>>   I have the rule for table
>>   CREATE RULE del_jobs_del_activity AS ON DELETE TO jobs DO
>> DELETE FROM activities WHERE job_id = OLD.job_id;
>>
>>   When i call:
>>   DELETE FROM jobs WHERE job_id IN (SELECT DISTINCT job_id FROM
>> activities WHERE load_no = OLD.load_no)
>>   This query does not delete the record from jobs but delete the
>>   record by rule.

Best regards,Andriy                            mailto:andriy.pyrozhenko@vanjaonline.com




Re: Question

From
"Christopher Kings-Lynne"
Date:
> Hello Christopher,
>
>   I don't need delete record from jobs when I delete record from
>   activity. But I need delete record from activity when I delete the
>   record from jobs and I need to find this job_id in activities table!
>   What must I do?
>
> CKL> Question: Why don't you just set up a foreign key that will
> achieve exactly
> CKL> the same thing without using RULEs?
>
> CKL> ie. ALTER TABLE activities ADD FOREIGN KEY (job_id)
> REFERENCES jobs(job_id)
> CKL> ON DELETE CASCADE;

My ALTER TABLE statement above will do exactly this.

It will delete activities when a job is deleted.  Try it in a test database.

Chris



Re: Question

From
Andriy Pyrozhenko
Date:
Hello Christopher,

>>   I don't need delete record from jobs when I delete record from
>>   activity. But I need delete record from activity when I delete the
>>   record from jobs and I need to find this job_id in activities table!
>>   What must I do?
>>
>> CKL> Question: Why don't you just set up a foreign key that will
>> achieve exactly
>> CKL> the same thing without using RULEs?
>>
>> CKL> ie. ALTER TABLE activities ADD FOREIGN KEY (job_id)
>> REFERENCES jobs(job_id)
>> CKL> ON DELETE CASCADE;

CKL> My ALTER TABLE statement above will do exactly this.

CKL> It will delete activities when a job is deleted.  Try it in a test database.
 But also need delete activities!


-- 
Best regards,Andriy                            mailto:andriy.pyrozhenko@vanjaonline.com




Re: Question

From
"Andrew G. Hammond"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 2001 November 23 02:55 am, you wrote:

> >> CKL> ie. ALTER TABLE activities ADD FOREIGN KEY (job_id)
> >> REFERENCES jobs(job_id)
> >> CKL> ON DELETE CASCADE;
>
> CKL> My ALTER TABLE statement above will do exactly this.
>
> CKL> It will delete activities when a job is deleted.  Try it in a test
> database.
>
>   But also need delete activities!
 Which is exactly what this will do.  When you ask for help, you might want 
to try what people suggest.  If you had tried what Chris suggested (twice!) 
you would have discovered that his suggestion solves the problem you have 
described.  Please respect the time and effort of volunteers answering 
questions in this forum.  You can do this best by devoting some time and 
effort to thinking about and learning from the responses posted.  At least 
try a suggested solution before complaining that it doesn't work!

- -- 
Andrew G. Hammond     mailto:drew@xyzzy.dhs.org   http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F                  613-389-5481
5CD3 62B0 254B DEB1 86E0  8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iEUEARECAAYFAjv/aroACgkQCT73CrRXhLEZJACeKGkoYQ65+yMO99rBFiizjSIz
YgcAli4uE+9/e5JkfP8DMqzeXdPMbRQ=
=F89I
-----END PGP SIGNATURE-----