January 14, 2004 at 4:14 am
HI,
This trigger displayes the error description no matter what infomation i put in. I have tried other ways but unfortunalty i don't have enoughf experiance to find a solution. If you have any guide lines or suggestion i would greatly appriciate it.
Franz: -
CREATE TRIGGER Tr_FunctionValidity
ON Function_Table
FOR INSERT, UPDATE
AS
BEGIN TRANSACTION
DECLARE @FDate DATETIME
DECLARE @FTime DATETIME
DECLARE @CountDate INT
DECLARE @CountTime INT
SELECT @FDate = FDate
FROM Function_Table
SET @CountDate = Count(@FDate)
SELECT @FTime = FTime
FROM Function_Table
SET @CountTime = Count(@FTime)
IF (@CountDate = 0 ) AND (@CountTime = 0)
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
RAISERROR ('There is already a booking for the entered date, Please enter a valid Date', 16, 1)
ROLLBACK TRANSACTION
END
January 14, 2004 at 6:15 am
Using COUNT() outside of a query will return 1, no matter what the argument (unless you try NULL), so neither of the local variables you test will ever equate to zero.
If I understand what you're trying to do, why not just create a unique index on (FDate,FTime)? (And why use two datetime columns rather than just one?)
In any case, it's generally a bad idea to try and use local variables like this in a trigger, as inserts and updates may add or change more than just one row.
--Jonathan
January 15, 2004 at 12:42 am
I don't know if it's just pseudo code, but you should be using inserted as your table name. It's the actual table data that's being inserted, even during the update. One caveat is that it could contain more than one record which will give you odd results unless you code for either.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply