script error checking

I have a situation where It′s necessary to update my customer′s database.
So I′ve built a litle executable that executes an updating script using the
osql tool during the installation process.
In the end, the program checks for any entry in a specific table containing
error lgo information.
To prevent users from using the application if any error occurs, I′ve
written some errors checking in the script, as follows:
DECLARE @.errorsave int
IF NOT EXISTS(SELECT nm_version FROM VerInfo where nm_version = 2)
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
ALTER TABLE .... /* first change */
SET @.errorsave = @.@.ERROR
IF @.errorsave <> 0 GOTO CONTROL_ERROR
ALTER TABLE .... /* second change */
SET @.errorsave = @.@.ERROR
IF @.errorsave <> 0 GOTO CONTROL_ERROR
-- update version info block
INSERT INTO [dbo].[VerInfo] (nm_version) VALUES (2)
COMMIT TRANSACTION
END
CONTROL_ERROR:
IF @.@.TRANCOUNT>0
BEGIN
ROLLBACK TRANSACTION
INSERT INTO [dbo].[LogInfo]([TX_Description])
SELECT description FROM [master].[dbo].[sysmessages]
WHERE error = @.errorsave
END
GO
The problem is:
There are some specific situations (like drop column) where, if an error
occurs, the script just stops running and does not perform the error log
saving routine, while, in other situations (like drop table) that saves the
error into "LogInfo" table, as expected.check out this link for a good discussion on SQL error handling:
http://www.sommarskog.se/error-handling-I.html

scouts, badges, and groups of badges (was "Please help")

Hi Guys,:rolleyes:

I am designing a database for scouts and i am stuck on the ERD(relationships design)...The situation is this: I have an entity 'scout' and entity 'badge'.
A scout can have many badges as obvious. However there are different classification(groups) of badges(main badges, activity badges,Staged Activity badges...). Each of these group of badges consists of different types of badges and finally a badge type has required tasks to accomplish to qualify for it!!

Is there anyone with a suggestion how to model these entities please??

migarich@.yahoo.frWhen you post your homework, please either provide the URL where the assignment was given, or at least scan in the handout you got in class that describes the assignment. It makes life so much simpler if we can do your homework for you in one pass, instead of completing what you've posted so far, only to have you realize that there is another part you need for full credit!

-PatP|||This is not actually an assignment but a system i am designing as a voluntary action for my local scout troup...and there is no specifications as such...the descriptions i have made are from my head and not some handouts as you may think...If there is any part of the description you do not understand i would be grateful to hear...as i said it is only part of the problem that i am having issues with.

Regards|||Ok, I see three detail level tables: Scouts, Requirements, Badges.

I see three linking (many to many) tables, Scout to requirement (showing that the scout has met a requirement), Badge to requirement (showing that the requirement is a pre-requisite for the badge), and Scout to badge (showing that the scout has earned the badge).

I see one "decorative" table, showing that a badge is of a given badge type.

-PatP|||Here is a sample model using natural keys. You can substitue surrogate keys if you'd like:
Table: Scouts
(ScoutName, [pkey]
etc...)

Table: BadgeClasses
(BadgeClass, [pkey]
etc...)

Table: Badges
(Badge, [pkey]
BadgeClass, [fkey to BadgeClasses]
etc...)

Table: ScoutBadges [implements many-to-many relationship between scouts and badges]
(ScoutName, [fkey to Scouts]
Badge, [fkey to Badges]
etc...)|||Hi Blindman,

This definetely makes sense...I was thinking more of the supertypes and subtypes...Would you say it is feasible?...Thanks

Here is a sample model using natural keys. You can substitue surrogate keys if you'd like:
Table: Scouts
(ScoutName, [pkey]
etc...)

Table: BadgeClasses
(BadgeClass, [pkey]
etc...)

Table: Badges
(Badge, [pkey]
BadgeClass, [fkey to BadgeClasses]
etc...)

Table: ScoutBadges [implements many-to-many relationship between scouts and badges]
(ScoutName, [fkey to Scouts]
Badge, [fkey to Badges]
etc...)|||Thank you very much...It is now clearer...How about replacing the requirement table by 'Scout_badge' which has 2 key fields as from scout and badge??

Thanks for you help|||Table: Requirements
(Requirement, [pkey]
Badge, [fkey to badges]
Description,
etc...)

Table: ScoutRequirementsCompleted
(ScoutName, [fkey to scouts]
Requirement, [fkey to Requirements]
DateCompleted,
etc...)

When a scout has completed all the requirements for a badge, grant him the badge and add a record to ScoutBadges|||But a Scout can only earn a badge once, and if I remember correctly, aren;t some badges prerequsities to other badges?|||But a Scout can only earn a badge once, and if I remember correctly, aren;t some badges prerequsities to other badges?A UNIQUE CONSTRAINT will take care of a scout only earning a badge once. Making the possesion of a badge a requirement for another badge takes care of the prerequisite.

-PatP|||Hi again,

there are various tasks/activities that for a badge requirement...
Thanks again|||Hi guys, I have a table called camp, Scout and Medical_File...i need to include the details of a scout medical file when the scout is booking a camp so we know what we need to take care of in different activities...does anyone know how to model this. The medical File is not currently linked to the camp at the moment!!
Thanks|||If there is only one medical file for each scout, then consider simplifying your schema by including the medical information in the scouts table.|||That is actually a good idea...i did that before and decided to move it to a separate entity as the enity scout seemed of a big table...

Yeah i have another prob...sometimes scouts go to a camp by patrol but also individually depending on who organised the camp...at the moment i had scout entity linked to camp via payment(camp payment)...do u think i should link patrol as well?

thanks|||Is a scout in only one Patrol?

You should probably store "snapshot" information about the scout and the patrol the belong to in your camp/scout attendance table, as some of the data may change over time (they may join a different patrol), but you would still want to know that they were in the original patrol when they attended the camp.