Triggers - Foreign Keys - Priority?

[Flawless_koder]

Technically i guess these two are the same thing - but i kind of need to know which will happen first.

I have an id field in table 1.
table 2 has a foreign key constraint on a field, referencing table1.id which cascades on delete.
if i put a trigger on delete for table 1 - does it have to be an BEFORE DELETE to occur before the cascade of table2.

I ask this because i plan on doing calculations on the
data inside table2 relating to the row being deleted, but don't neccessarily want the delete to be in the trigger since the function/sproc called by the trigger may also be called under other circumstances.

Do i have to use a "before delete" or will the cascade naturally wait for any triggers on the id to occur before starting?

Thanks

Flawless

[mulletboy2]

I'm just guessing, but I'd think that the cascade would happen first. Have you checked your DBMS help files/tutorials? You could always setup a couple of test tables to test it anyway.

I'd never encounter this problem - as a general principal, triggers are bad for this very reason.. The last operation you perform on a database may not be the last operation you explicitly performed (e.g if you perform an insert using a trigger after every insert, the last ID (@@IDENTITY in SQL Server) is not the record you inserted - I'm sure you can see the potential for problems here)... it can mean that system variables contain the wrong values etc...

Try to steer clear of the trigger if possible, and consider using a deleted_indicator field instead. You shouldn't delete data from a database in most cases... move archived records to another location if you want, but if you delete from the db, you lose any audit trails. You can still get the trigger if necessary using the deleted_indicator.. just do a trigger for update and/or insert.

[Flawless_koder]

In PostgreSQL you can access the triggering row from a trigger using OLD or NEW.

I'm planning on creating an entry in another table before deleting from this table (and yes - it must be deleted, or at least i have written it so it must be [ and if you say your ol' "that's bad design for a start" routine i swear to the holy son of god i'll stick a goat up your bottom, hooves and all]) but the entry that goes into the other table needs to do some calculations, make some changes etc.
The function can also be called seperately (ie not triggered) but with a variable replacing the OLD reference.

Flawless

[Flawless_koder]

More to the point, however, i shouold have realised that the cascade is on delete and the trigger is on before delete, therefore the trigger occurs first.
(in contradiction to your comment)

Flawless

[Flawless_koder]

This could be shown by the following ( postgres ):

CREATE TABLE test (
	id 	serial Primary key,
	name	varchar(10)
	);

CREATE TABLE refs (
	rem_id	int,
	surname	varchar(10),
	CONSTRAINT refs_id FOREIGN KEY (rem_id) REFERENCES test (id) ON DELETE CASCADE);

CREATE FUNCTION plpgsql_call_handler() RETURNS OPAQUE AS '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C';

CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL';

CREATE FUNCTION before_cascade () RETURNS opaque AS '
	DECLARE
	row_deleting test%ROWTYPE;
	row_referenced refs%ROWTYPE;
	BEGIN
		SELECT INTO row_deleting * from test WHERE id=OLD.id;
		RAISE NOTICE ''Deleting %'', row_deleting.name;
		FOR row_referenced IN SELECT * from refs WHERE rem_id=OLD.id LOOP
			RAISE NOTICE ''Affects %'', row_referenced.surname;
		END LOOP;
	RETURN OLD;
	END;'
language 'plpgsql';

CREATE TRIGGER show_me BEFORE DELETE ON test FOR EACH ROW EXECUTE PROCEDURE before_cascade();

Flawless

[mulletboy2]

. o 0 ( my comment about "RTFM" was still right though, eh? )

[mulletboy2]

And OLD or NEW is great, providing all of your SQL takes that into account... If you modify a row, then that update causes a trigger to be fired, does any SQL accessing the details of the last operation REALISE that? Does any developer in the future realise the trigger is there, and do you EVER listen to a word I say?

[Flawless_koder]

Yes Yes No

[Flawless_koder]

That is to say that triggers are listed on tables when \d is used.
Any query being performed on the data would not really need to know what the trigger was doing unless the trigger was about to change some vital data on a field of that row / related row. If such were true then you'd use a lock field i suppose, but i wouldn't worry about that personally - scalability is something people who are "bigger than me in the shower" worry about that - you've seen - i don't have anything to worry about - it's not getting bigger </sexual inuendos (?!)>

Flawless