|
In SQL Server the trigger has two tables "inserted" and "deleted". These contain all columns in the table effected. Note for a delete query inserted is empty, for an insert deleted is empty.
|
|
|
|
|
thanks for your attention . I know that we have those tables in triggers . I am not doing dml action to effect them .
I have a query that returns some "Personnel Codes" that these codes should be in the trigger as input . Now I am looking a way to solve this problem .
thanks
|
|
|
|
|
It is difficult to decipher what you really mean but let me take a a guess. You have a query that returns Personnel codes... I am assuming that you want to do something with personnel codes. If you are to use a trigger, then you must be peforming inserts, updates or deletes in a table. You said you're not doing DML, so triggers don't apply!
You don't describe the problem in sufficient detail but I'll make a guess. You're getting some values using a query and you want to do something based on the results returned. I think you need to do everything in a stored procedure.
Your stored procedure would have a section that queries for personnel codes.
It would also have a section that performs some action based on the personnel codes.
If you would describe the problem in sufficient detail, it would be easier to help. I would also suggest tha you avoid describing the problem in terms of the solution i.e. your problem description presupposes that triggers are the answer. Just state the problem in sufficient detail.
|
|
|
|
|
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?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
