SProcs and Triggers, the wrong way

snoofle

I've mentioned I'm doing an after-the-fact code review to see what some of my younger coworkers did during this last release push.

I just found this:

create or replace procedure Schema.TheSproc1(...) is
begin
  execute immediate 'drop trigger Schema.TheTrigger';
  execute immediate 'create or replace trigger Schema.TheTrigger '  ||
  'after insert on Schema.TheTable for each row ' ||
  'begin insert into TheSchema.SubordinateTable values(<subordinate data directly derivable from the parameters>); end';
  insert into TheSchema.TheTable values(<data set 1>);
end;

create or replace procedure Schema.TheSproc2(...) is
begin
  execute immediate 'drop trigger Schema.TheTrigger';
  execute immediate 'create or replace trigger Schema.TheTrigger '  ||
  'after insert on Schema.TheTable for each row ' ||
  'begin insert into TheSchema.SubordinateTable values(<a nearly identical set of subordinate data also directly derivable from the parameters>); end';
  insert into TheSchema.TheTable values(<data set 2>);
end;

...repeated a total of 15 times

I should mention that the subordinate data is all of the same form, and could simply be used directly in insert statements as it's directly derivable from the params already being passed to all of the stored procedures.

When I inquired, the developer stated that we need to ensure referential integrity and that should be done with triggers.

boog

I like it. Multiple stored procs that change a single trigger, so the trigger's behavior depends on which procedure was called last. I'm curious to know what happens if you call more than one stored proc at the same time.

@snoofle said:

When I inquired, the developer stated that we need to ensure referential integrity and that should be done with triggers.

I hope you told him: If you want a trigger, just define a fucking trigger. A stored procedure is not the correct method for doing this.

snoofle

@boog: actually, I rewrote the whole thing in 4 insert statements and one stored proc. It took three of us to impress upon this - person - that simply using parameters and the occasional formula achieved the same result.

C_Octothorpe

@snoofle said:

@boog: actually, I rewrote the whole thing in 4 insert statements and one stored proc. It took three of us to impress upon Nagesh that simply using parameters and the occasional formula achieved the same result.

FTFY

Sutherlands

@C-Octothorpe said:

@snoofle said:
@boog: actually, I rewrote the whole thing in 4 insert statements and one stored proc. It took three of us to impress upon bridget99 that simply using parameters and the occasional formula achieved the same result.
FTFY

FTFY

C_Octothorpe

So, when are we starting the fund for snoofle's antidepressants?

galgorah

@C-Octothorpe said:

So, when are we starting the fund for snoofle's antidepressants?

Forget antidepressants, if this goes on much longer he may need a lobotomy!

Ibix

@galgorah said:

@C-Octothorpe said:
So, when are we starting the fund for snoofle's antidepressants?
Forget antidepressants, if this goes on much longer he may need a lobotomy!

It ain't Snoofle who needs the lobotomy. I'm rather getting the impression that most of his copro-grammers are not generally allowed sharp objects or shoe laces but are, inexplicably, permitted computers.

havokk

@snoofle said:

the developer stated that we need to ensure referential integrity and that should be done with triggers.

Ye flaming gods.

If you need to ensure referential integrity (a good thing) then you do it with declarative referential integrity.

Triggers are a last line of defence, for stuff that absolutely cannot be done any other way

Weps

At least there (at Snoofle's place) there is some integrity.... I'm currently working on a database with a float as primary key. And no, it is not defined as being a primary key in the database....

Sutherlands

@Weps said:

At least there (at Snoofle's place) there is some integrity.... I'm currently working on a database with a float as primary key. And no, it is not defined as being a primary key in the database....

But really, who among us hasn't done this at least once?

havokk

@Sutherlands said:

@Weps said:
At least there (at Snoofle's place) there is some integrity.... I'm currently working on a database with a float as primary key. And no, it is not defined as being a primary key in the database....
But really, who among us hasn't done this at least once?

Ummm.... me.

If I were working on a database with a float as a pseudo-key and no primary key constraint, I'd think I'd just stop referring to it as a "database". I'd use "spreadsheet" or "repository" or perhaps "sepulcher". If someone asked me why I don't call it a database I'd say "it can't be a database - databases are normalised". Afterall, I'm a technical person, I'm generally being paid for technical skills, so I should be technically accurate. :-)

Sutherlands

@havokk said:

If someone asked me why I don't call it a database I'd say "it can't be a database - databases are normalised". Afterall, I'm a technical person, I'm generally being paid for technical skills, so I should be technically accurate. :-)

If you were technically accurate, you'd mention that some people denormalize databases on purpose

Database normalization - Wikipedia

havokk

@Sutherlands said:

@havokk said:
If someone asked me why I don't call it a database I'd say "it can't be a database - databases are normalised". Afterall, I'm a technical person, I'm generally being paid for technical skills, so I should be technically accurate. :-)
If you were technically accurate, you'd mention that some people denormalize databases on purpose

Database normalization - Wikipedia

True, but I sacrifice accuracy for passive-aggressiveness.

error

I confess, years ago I was told to integrate two entirely dissimilar systems (a web content management system and an ecommerce system) in a very short time frame.

I accomplished this integration by dropping some tables from database A, replacing them with views to tables in database B, and using INSTEAD OF triggers to make sure the two shared a common data store. Even though one thought it was working with a hierarchy and the other didn't (the logic in the trigger rebuilt the tree database A had a representation of, and a dummy table held the fields missing from database B.)

boomzilla

@havokk said:

True, but I sacrifice accuracy for passive-aggressiveness.

Perhaps the most succinct summary of this forum that I have ever read.

Scarlet_Manuka

@boomzilla said:

@havokk said:
True, but I sacrifice accuracy for passive-aggressiveness.
Perhaps the most succinct summary of this forum that I have ever read.

Surely not. It completely excludes the whole "pedantic dickweed" contingent, who [b]use[/b] accuracy for passive-aggressiveness.

Sutherlands

@Scarlet Manuka said:

@boomzilla said:
@havokk said:
True, but I sacrifice accuracy for passive-aggressiveness.
Perhaps the most succinct summary of this forum that I have ever read.
Surely not. It completely excludes the whole "pedantic dickweed" contingent, who use accuracy for passive-aggressiveness.

Succinct does not mean accurate.

DaveK1

@Sutherlands said:

@Scarlet Manuka said:
@boomzilla said:
@havokk said:
True, but I sacrifice accuracy for passive-aggressiveness.
Perhaps the most succinct summary of this forum that I have ever read.
Surely not. It completely excludes the whole "pedantic dickweed" contingent, who use accuracy for passive-aggressiveness.
Succinct does not mean accurate.

Well if accuracy isn't important, then I can give a far more succinct summary of the forum:

Zero chars. Beat that!

boomzilla

@Sutherlands said:

@Scarlet Manuka said:
@boomzilla said:
@havokk said:
True, but I sacrifice accuracy for passive-aggressiveness.
Perhaps the most succinct summary of this forum that I have ever read.

Surely not. It completely excludes the whole "pedantic dickweed" contingent, who use accuracy for passive-aggressiveness.

Succinct does not mean accurate.

"Succinct" and "pedantic dickweed" have similar relationships to the word "accurate."

TheCPUWizard

@boomzilla said:

@havokk said:
True, but I sacrifice accuracy for passive-aggressiveness.

Perhaps the most succinct summary of this forum that I have ever read.

But remember, succinct is verbose for terse.

TheCPUWizard

Back about 8 years ago I had to work on a system that ONLY allowed writes to "temporary" table (not temportary in the RDBMS sense, but realy tables for holding content temporarily) and then triggers fired which loaded all of the actual tables. The DBA claimed it was for both security and for "transactional purposes"

dhromed

@TheCPUWizard said:

But remember, succinct is verbose for terse.

Also remember that terse is verbose for curt.

DaveK1

@dhromed said:

@TheCPUWizard said:
But remember, succinct is verbose for terse.

Also remember that terse is verbose for curt.

And recall that curt is long for wee.

dhromed

@DaveK said:

@dhromed said:
@TheCPUWizard said:
But remember, succinct is verbose for terse.

Also remember that terse is verbose for curt.
And recall that curt is long for wee.

eh