Обсуждение: 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) 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
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
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 >
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
> 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
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
-----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-----