|
The trigger will file once for each statement. You should always write your triggers with the assumption that the inserted and deleted virtual tables will contain multiple rows.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Richard, thanks for that. I need to schedule some time to look into them. I know they're basically simple concepts but it's in the doing that you understand them.
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
|
|
|
|
|
Triggers spit are a support nightmare, especially for a new developer. If you break a trigger (changing/removing a field) it can be extremely difficult for support to identify the problem (it can be bloody difficult for the silly bastard who wrote the trigger).
We do use the horrible things but only for audit purposes NEVER to implement business logic, that lives in a stored proc that does what it is told when it is told and everyone knows where to find it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: that lives in a stored proc that does what it is told when it is told and everyone knows where to find it.
I wish our database at work was so obliging and obedient. 
In a previous job I remember problems with a trigger when looking for differences in the before and after images. I never worked on a more convuluted POS in my life. It was the kind of trigger that had an unwritten rota as to which mug of the month got as a prize.
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
|
|
|
|
|
I dictate the standards on our DBs (mostly) so triggers and underscores struggle to survive and are hunted down and eliminated whenever I get really bored. This does tend to piss off the offending Dev who ignored the conventions.
So today they ganged up on me and we will move to MVC instead of WPF for next years crop of apps- bastards!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Richard Deeming wrote: inserted
Richard Deeming wrote: deleted
Brings back old memories. A stupid bugger rejected me in an interview when I could't answer the question "what are magic tables?". I knew these tables and how to use them, it's just that he expected me to know that they're called magic tables (even after a decade, I haven't heard anyone else calling them by that name).
|
|
|
|
|
Well, by the highly scientific method of Google-fighting:
your interviewer was an idiot. 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Without going into gruesome detail, I would like to get some ideas on how I might approach this problem.
Given that I have a classic master-detail relationship in my database, lets call the tables Master and Detail. There is a master ID, and a detail ID for every row and every detail record has a reference to the master ID which it belongs.
If master record A has 3 detail lines (Part 1, Part 2, Part 3) and master record B also has those same detail records (Part 1, Part 2, Part 3) then I want to indicate that master record A and B are identical.
Can anyone suggest a method to do this ?
My initial idea is to use 2 SQL cursors where I would be looping through the data with Cursor1 from top to bottom, then use Cursor2 to scan the same data for each occurence of Cursor1.
Jeeze, sounds kind of lame. 
There must be a better way.
Any suggestions would be greatly appreciated.
|
|
|
|
|
Something along these lines might get you started:
SELECT
A.ID As OriginalID,
B.ID As DuplicateID
FROM
MasterTable As A
CROSS JOIN
MasterTable As B
WHERE
A.ID < B.ID
And
Not Exists
(
SELECT
(relevant columns)
FROM
DetailTable
WHERE
MasterID = A.ID
EXCEPT
SELECT
(relevant columns)
FROM
DetailTable
WHERE
MasterID = B.ID
)
And
Not Exists
(
SELECT
(relevant columns)
FROM
DetailTable
WHERE
MasterID = B.ID
EXCEPT
SELECT
(relevant columns)
FROM
DetailTable
WHERE
MasterID = A.ID
)
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Wow, that looks very interesting.
I will see if I can get it to work.
|
|
|
|
|
Normal way to acknowledge thanks is to up vote the answer - a message is appreciated as well.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
An up-vote before you know whether the answer works might be a bit premature.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
You can use the GROUP BY clause on the Detail table with Part1, 2 and 3 in the group clause and take the distinct DetailID and MasterID from the table. But Group By requires an aggregate function and I'm not sure how to take distinct values using it. If you are sure you need only 2 matching rows, you can use MIN and MAX functions of course.
|
|
|
|
|
You could use something like the except or minus(depending on your flavour of sql) clause to find differences between queries for rows referencing the same master ID.
This would return differences, those rows not returned would be equal.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
hi
I can i use "rank() over" OR "row_number" in sqlite ?
Thanks in advanced!
|
|
|
|
|
Did it even occur to you to type that into google[^], it seems to have been answered many time in great detail and 1 looks like it has a work around!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i have googled a lot but no good solution !
can give samll example ?
thanks in advanced
|
|
|
|
|
Hello!
I have the following problem: I need tips on structuring a product database. This will be a database of software products and what I specifically need help with is the following:
Say you have a software product in 4 editions: Free, Lite, Standard and Enterprise.
I need to model all four kinds possibly in one table and not just that, I also need to make sure that changes are trackable, i.e. if I put out a new minor version for one of the editions, I want to be able to track that there's been a change and what the change has been.
Is it clear?
Can you please advise and help?
Thank you!
A
|
|
|
|
|
This isnot answerable in a forum post, you are going to have to do some research, here is some google foo[^] some where there is an entire collection of sample schemas for different industries. This may get you started but you are still going to need to do a lot of reading.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
DeerBear wrote: I need tips on structuring a product database.
Learn the trade before you learn it's tricks. A decent database-design is normalized.
DeerBear wrote: Free, Lite, Standard and Enterprise.
I need to model all four kinds possibly in one table and not just that, I also need to make sure that changes are trackable
Having four different licenses doesn't complicate design - it's not even clear whether or not the licenses will differ in terms of their datastructure.
Tracability has nothing to do with the design; when you change it, make a note on a piece o' paper describing what you changed. Do that every time, and you'll have a list of changes.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi!
I am not new to databases, far from it, I was more
looking for speculative insight on how best to do
things. The reason for that is that this product
will also be doing quite a bit of OLAP and that
usually means your database has to be de-normalized.
The more I look into it, though, the more I realise
there is no way to get minimal de-normalization so
that I can use the same table for two purposes
without putting an overloaded burden on development.
The tracking will have to be done in AUDIT tables with
INSERT triggers. All of the solutions I could think of to
avoid this are, effectively, unusable.
HOWEVER, if you happen to have ideas on how to do
that, I would be interested to know 
This is a sample of my structure(to give you an
idea of the path I am taking):
CREATE TABLE PRODUCTS (
ID INTEGER NOT NULL,
PRODUCT_NAME VARCHAR(255) NOT NULL,
PRODUCT_DESCRIPTION BLOB SUB_TYPE 1 SEGMENT SIZE 80 NOT NULL,
DOWNLOADABLE INTEGER NOT NULL,
PRODUCT_DISCONTINUED INTEGER NOT NULL
);
CREATE TABLE PRODUCT_DETAILS (
ID INTEGER NOT NULL,
PRODUCT_ID INTEGER NOT NULL,
HIGH_VERSION INTEGER,
LOW_VERSION INTEGER,
EDITION VARCHAR(255),
DOWNLOADABLE INTEGER NOT NULL
);
CREATE TABLE PRODUCT_HISTORY (
ID INTEGER NOT NULL,
PRODUCT_ID INTEGER NOT NULL,
VERSION_HIGH INTEGER DEFAULT 1 NOT NULL,
VERSION_LOW INTEGER DEFAULT 1 NOT NULL,
EDITION VARCHAR(255) DEFAULT '' NOT NULL,
DISCONTINUED INTEGER DEFAULT 0 NOT NULL,
DOWNLOADABLE INTEGER DEFAULT 1 NOT NULL
);
Oh and before someone starts whining about the lack of
primary or foreign keys: this is Firebird, those
elements are part of ALTER TABLE statements.
They're there but unreported for brevity
|
|
|
|
|
DeerBear wrote: The reason for that is that this product
will also be doing quite a bit of OLAP and that
usually means your database has to be de-normalized.
So not a complete newbie then - excellent.
Most OLAP solutions use a different database than the production DB and the data is denormalised periodically (daily) into said OLAP Db.
While I don't know firebird I would not use the audit process/triggers spit to retain/create your history. I would build it into the business process where the application generates the history records (you may not want trivial changes to the data to create a history record).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: Most OLAP solutions use a different database than the production DB and the data is denormalised periodically (daily) into said OLAP Db.
I am not an OLAP expert, by far, but in the very few experiences I've had where we
needed cubes, I've found that you don't always need another db.
What I am trying to achieve is a middle ground between a complete data
warehouse and a "normal" database. I don't mind, in this resepct, to lose less
meaningful data, as far as the most important data is dealt with appropriately.
I would like to add some new tables, prefixed by OLAP_ so that I know those are
related to my cubes, and do something like this:
CREATE TABLE OLAP_PROD_HISTORY(
ID INTEGER NOT NULL,
PRODUCT_ID INTEGER NOT NULL,
VERSION_HIGH INTEGER DEFAULT 1 NOT NULL,
VERSION_LOW INTEGER DEFAULT 1 NOT NULL,
EDITION VARCHAR(255) DEFAULT '' NOT NULL,
DISCONTINUED INTEGER DEFAULT 0 NOT NULL,
DOWNLOADABLE INTEGER DEFAULT 1 NOT NULL,
CHANGE_DATE DATE NOT NULL,
CHANGE_TYPE INTEGER NOT NULL DEFAULT 0
)
The original idea though was trying to keep everything in the ordinary tables but I do not
think that's doable in any easy way - unless someone comes up with a brilliant idea, that is!
Do you see my point?
Regards,
A
|
|
|
|
|
I must be missing something because that seems pretty trivial.
(If you want to track actual changes to code, rather than just something like release notes, then you should be using a version control system.)
And why does it matter if it is OLAP or denormalized? How many entries entries based on actual business cases (not developer fantasy) will it contain?
|
|
|
|
|
jschell wrote: How many entries entries based on actual business cases (not developer fantasy) will it contain
Damn missed that! This whole thing will be so trivial it does not matter how he builds the bloody thing!
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
