|
Hi . thanks for your attention .
The problem is I have list of personnels that every month give some money to the bank of our firm . If their balance reaches to for example 100$ then they can enter to the list of people who can use loan (TurnsTable).
Now I have a SP that each month adds for example 20$ to all of our staff's Balance all in the same time . this is an important insert to my DB because when an insert takes place I want to write a trigger to check the total balance after insert.If the balance would reach to the special amount(100$) then this person's Personnel code must be inserted to the TurnsTable which maintains the list of personnel who wants loan . because of this i needed to know that can i send parameters to the triggers ?
what do you suggest to my problem ? How can i solve it?
|
|
|
|
|
This is not a good idea for a trigger. You could create an AFTER INSERT trigger that calculates a new balance and if that balance exceeds $100, to then insert a record into another table. You now have the potential for creating a circular operation which is not good.
The previous suggestions for using a procedure to perform the balance calculation and to then insert or delete based upon the result of that calculation makes much more sense and will be prone to less errors.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
I would create a stored procedure that takes two parameters: personnel code and the amount by which to update the personnel balance. I would also use a transaction to ensure that no two users update the same record at the same time
CREATE PROCEDURE updateBalace
@personnelCode int
@amount decimal
AS
SET @actionAmount decimal=100.00
BEGIN TRAN
UPDATE Personnel SET Balance=Balance+Amount WHERE PersonnelCode=@personnelCode
IF EXISTS(SELECT Balance FROM Personnel WHERE Balance>@actionAmount AND
PersonnelCode=@personnelCode)
BEGIN
END
IF @@ERROR >0
ROLLBACK TRAN
ELSE
COMMIT TRAN
My code is not kosher but you get the general idea.
|
|
|
|
|
SilimSayo wrote:
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Thanks Chris.
I needed to amuse myself a little.
|
|
|
|
|
THE TRIGGER WON'T HAVE PARAMETERS.
|
|
|
|
|
i initially got unable to load, file is being used by another program so i stopped the sqlserver service and tried again..
i now have this...
Error 26 Error locating server/instance specified...
the servers there and ive pointed it directly at the mdf,
what else could be happening here...
|
|
|
|
|
Its okay, fixed it..
needed to start the sqlserver services, had it set to automatic, apparently it doesnt like it
|
|
|
|
|
|
hi
how to make self backup to sql-server-2008 ?
i need that backup every 2 hours
and if the database is in work - it will be a problem ?
thanks in advance
|
|
|
|
|
Gali1978 wrote: and if the database is in work - it will be a problem
Why! Unless you have a high performance OLTP database backup should have minimal effect on the database.
Do some reading on backup strategies, it is a large and complex subject with numerous options to achieve your requirements.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Look into
1) Mirroring - One of several strategies that give you fail-over protection
2) Transaction log backup - This is the preferred method for frequent backup needs. You do one full backup then use the transaction log backup to update a database.
|
|
|
|
|
Hi I have a group of tables that have some relationship with each other .
HardwareType( HardwareTypeID (pk) , Name )
Hardware (HardwareID , Model , ...,HardwareTypeID (FK))
Properties(PropertyID (PK) , Name , HardwareTypeID)
Values( ValueID (PK) , ValueTitle , PropertyID (FK))
Hardware-Value( PVID (PK identity), ValueID (FK), HardwareID(FK))
with these in my design i have a circuit in my design and this is very bad for my update and delete operations . so i want to know that should i have Primary keys and relationship always in my tables or i can avoid using primary key and relationships in my design under some circumstances like the
Hardware-Value( PVID (PK identity), ValueID (FK), HardwareID(FK))
can i omit the foreign key constraint in this design without loosing the normal form of my database ?
thanks
|
|
|
|
|
You shouldn't, the foreign keys are forcing you to insert, update and delete in the right order which will make sure you don't get any database corruption.
If you are using Oracle you may read up on "deferrable constraints" where the checking is deferred until the commit. But I recommend that you keep the foreign keys.
|
|
|
|
|
yousefshokati wrote: can i omit the foreign key constraint in this design without loosing the normal form of my database ?
No, it needs a primary key to be considered a normalized database. You could use the steps as described in the wikipedia to validate the model; inconsistencies should disapear when your model hits 3NF.
Can you show us some example-data in those tables? It's easier to validate the model if there's a sample-population.
Bastard Programmer from Hell
|
|
|
|
|
If you ignore key constraints your database will shortly deteriorate into a pile of garbage. While it is convenient during development and your business layer SHOULD cater for all your relational requirements having a rigid set of relationships in your data structure will INSURE that your database is not a pile of steaming poop!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You must be careful how you create your tables. Make sure you have the right entities identified and then normalise them and create your tables accordingly.In some cases, the foreign key becomes part of the composite key(i.e. a multi-column primary key)
Since I am not familiar with what you have in mind, I will use a different example. Consider a database for students, courses, and registration
Student(StudentId, FirstName,LastName, DOB, SIN....)
Course (CourseID, CourseName)
Semester(SemesterId, StartDate, EndDate)
CourseReg(StudentId,CourseID,SemesterID)
The primary keys are bold and underlined.The primary key for CourseReg is a composite key made up of foreign keys. Foreign keys are not always used as part of the composite key; in general use the fewest possible columns in a composite key while maintaining normalisation(3NF is usually good enough)
|
|
|
|
|
Hello . Thanks for your help .I ask this question because i had a problem in my own design
I have 5 entities that are related to each other and with foreign keys that i am sure are needed < there is a Circle in my design and this circle causes anomalies in the database and for this reason database prevent the DML actions in some of my tables .
I am going to ask a total question from you
: What should I do to solve the problem with the circles that appear because the foreign key constraints
?
thanks
|
|
|
|
|
I am not sure what you mean by "circles" but I suppose you mean some sort of cyclic constraints.
Before you create tables, you need to list the entities first. Entities are things tangible or intangible for which you want to capture.Also, determine what kind of information each entity should contain. At this point you're not thinking of tables. Once you have determine the entities,
then think of them as tables, and then normalise them to 3NF.
Since I am not an engineer, I can't figure out what the entities were to start with. So, go back to the point before you created those tables, what were the entities or objects whose information you wanted to capture?
|
|
|
|
|
May be to help be better, explain what these tables are for
Properties(PropertyID (PK) , Name , HardwareTypeID)
Values( ValueID (PK) , ValueTitle , PropertyID (FK))
Hardware-Value( PVID (PK identity), ValueID (FK), HardwareID(FK))
|
|
|
|
|
yousefshokati wrote: and with foreign keys that i am sure are needed
Based on what you posted I am sure that some are are not needed.
I seriously doubt that there is any reason for Properites to have HardwareTypeID. That is just an enumeration which can stand on its own. You tie the enumeration to something else via Values and that is sufficient.
|
|
|
|
|
I'm using VS2008 with C# and SQL CF v3.5 to create an inventory application for a mobile computer. I have never worked with SQL Compact before, but I'm pretty sure I've got it right from the database perspective. I've added my DataSource, TableAdapters, queries to fill my ComboBoxes, etc. When I deploy to my WM 6.5 device, I've made sure that the SQL Compact CAB files have been installed, and that the database exists on the device. When I use any of the ComboBoxes in the form, they are all blank and none of them are populated. In VS 2008 when I 'Preview Data' it's all there. Not sure where to go from here to debug?
|
|
|
|
|
Hello all,
I dont know it might be an easy thing but i didnt work it around.
I have two tables and want to join them.
Normally my join statement is as follows:
SELECT c.Name, co.Name
FROM City C
JOIN Country co ON c.ID = co.CID
but this time the columns in the columns in the tables that has relation is similar but with one difference.
C.ID = '123'
co.CID = 'c123'
so how can i join this;
SELECT c.Name, co.Name
FROM City C
JOIN Country co ON c.ID = 'c' + co.CID
something like that is it possible? or i need to accomplish what i am trying in two different statements?
Thank you very much for your help
|
|
|
|
|
Your second join works on some systems. Did you try it on your system?
|
|
|
|
|
Yes I have tried but it is not accepting it.
When i try it I am getting thois error:
Dynamic SQL Error
expression evaluation not supported
Strings cannot be added or subtracted in dialect 3
|
|
|
|