Hi every body.Could any sql sever expert show me how i can create a sql server 2000 trigger for this constraaint as this is my first time trying to create a trigger. I got many tables in my sql server. I want the trigger fires when a user tries to enter hours for an employee in hours worked table if he did not enter amount for same employee and same project in hourly wages table.(in another word amount should be enterd before hours)I want the trigger sends a massage and tells the user to enter amount before entering hours.Thanks.

http://i5.photobucket.com/albums/y18...nstraint.jpg

=====> table reletionships pic

You will want to do something like this:

If (SELECT hw.amount FROM [hourly wages] hw, [Hours Worked] hrw WHERE hw.employee = hrw.employee AND Employee = 10) = 0
BEGIN
        Select @ErrNo = 30001,
               @ErrMsg = 'Insert Denied! The corresponding Hourly Wages has not been entered.'

    RaisError @ErrNo @ErrMsg
    Rollback Transaction
end

Many thanks to u reply. how to define this trigger ? should i
just paste it in query analyzer ?could u be a little more specific ?Furthermore,could u tell me how i can trap the tirigger error in the client site which is access 2000 in my case and display the erro massage. Thanks

In Enterprise Manager, right mouse click on the table in which you want to create the trigger (hours worked, i think) and from the context menu select All Tasks -> Manage Triggers. In the Trigger Properties windows, replace the [TRIGGER NAME] with an actual name for the trigger, such as CheckHourlyWages, and then on the FOR line, delete the UPDATE and DELETE words. Then below the AS, paste my code above.

Now, for the client question, what are you using to develop the client application?

Many Many thanks for your helpfull reply. Well i followed what u told me. When i click on check syntax in trigger properties after i pasted
the code i get the following error:


Error 137: Must declare the variable @ErrNo.Must Declare the variable @erNo . I be happy if u help me fix this error. I use MS access 2000 forms for the client site and i want to trap the error that get generated by the trigger and prompt the user with the massage that he must first enter amount in the hourly wages table. Thanks

I am sorry, i forgot that piece of code. YOu need to declare all variables, so just before the

If (SELECT hw.amount FROM [hourly wages] hw, [Hours Worked] hrw WHERE hw.employee = hrw.employee AND Employee = 10) = 0
BEGIN
    Declare @Errno int,
             @ErrMsg varchar(255)
    Select @ErrNo = 30001,
           @ErrMsg = 'Insert Denied! The corresponding Hourly Wages has not been entered.'

    RaisError @ErrNo @ErrMsg
    Rollback Transaction
end

OK, now for the client, since you are using MS Access 2000, are you using a form to enter data? What does the code you are using to add teh record look like? Or, is it a bound form?

Many thanks for u reply. I tried to run it again this time i get this error :

Error 209: Ambiguous column name Émployee. It is very strange!Furthermore, u used employee 10 in the select statement of trigger. Is there a way to change that so it holds the current employee number that we want to enter hours worked for?
Well for entering data i am using bounded forms so i do not now how i can trap the error casued by the triggers once it workes.I be happy if u help me fix these erros and issues.Thanks