>> Here is my attempt at the code... <<
I am sure that someone will kludge something really ugly and complex
for you, but the real problem is that you are still an assembly level
programmer trying to move to an abstract high level language.
1) Stop putting that silly "-tbl" prefix on table names! This is
saying that your table models pieces of furniture that belong to user
accounts, according to ISO-11179. There is only one data structure in
SQL anyway. What you want is a name that tells us what set of
entities or relationship the table models; since it is a set,
something like "UserAccoun ts" is fine.
Volumns are not anything like Fields; until you know and think in
terms of rows and columns, you will never "get it" and will continue
to write BASIC in SQL.
2) SQL is a declarative language and probably the first such animal
you have ever seen. You tell it WHAT you want and it figures out HOW
to get it. This is a huge advance in programming. If the data
changes, the query stays the same but the execution plan changes with
you coding anything new.
What I actually have is 19 columns. 18 of them are bit value 1 of
them
char. The 18 represent 9 pairs. Each pair represents requested and
created. <<
ARRGH! Assembly language and punch card programming!! Think
relational, not physical. At best from this description, you have
nine attributes which ought to be in one column each; having "half an
atribute" is a design flaw called attribute splitting. At this point,
I have to guess at specs, but it looks like each attribute has two
values (requested and created). NEVER, NEVER, NEVER use BIT data (I
have an article due out soon in DBAzine with some of the many
reasons).
Sit down and design a status code with all the values you need; you
have
"requested" and "created", so nothing is needed for "failed" or
"rejected" in your situation?
The right way to do this is NOT with a trigger (procedural code!! The
HOW of the answer), but with a VIEW that will compute the status
(declarative code!! The WHAT of the answer).
This is for a new user account form whereby a user would request
access
to certain systems (administrated in diffrent areas) and the request
status would stay in an active state until all of the accounts were
created by the various administrators. <<
Now we have specs and can see that you don't know about First Normal
Form (1NF). The nine pairs are a repeating group in the data model.
Let us "flatten" out the table.
CREATE TABLE UserAccounts
(user_id INTEGER NOT NULL -- tables relate to each other, files don't
REFERENCES Personnel (user_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
admin_area INTEGER NOT NULL -- tables relate to each other, files
don't
REFERENCES Administration (admin_area)
ON DELETE CASCADE
ON UPDATE CASCADE,
request_status INTEGER DEFAULT 0 NOT NULL -- zero is "requested, not
granted"
CHECK (request_status IN (..)) -- columns have
constraints,fie lds don't
PRIMARY KEY (user_id, admin_area));
Your report:
CREATE VIEW RequestStatus (user_id, total_status)
AS
SELECT user_id, MIN(request_sta tus)
FROM UserAccounts
GROUP BY user_id;
Add or drop admin areas as you wish; add more status codes easily; DRI
actions maintain the schema for you. The schema is a WHOLE, not a
collection of files that stand alone -- design the whole!
