Well I can't see what's wrong, can you? 2

[williamu]

This very simple trigger :

CREATE TRIGGER Booking_Duration ON Booking FOR INSERT, UPDATE AS
SET DaysStay = (SELECT DATEDIFF(dd, FromDate, ToDate))

Fails with this error:

Server: Msg 170, Level 15, State 1, Procedure Booking_Duration, Line 2
Line 2: Incorrect syntax near '='.

I can't see what's wrong with it, but whatever it is it must be subtle, as both DaysStay and the return value of DATEDIFF() are INT.

Can anyone shed some light on this for me please.

 

[jbenson001]

Is this the whole trigger? Set would have to be used with an UPDATE clause.

 

[williamu]

Jeez, that was quick thanks, and it is the complete trigger.

But if I remove the SET from the statement I now get:

Server: Msg 170, Level 15, State 1, Procedure Booking_Duration, Line 2
Line 2: Incorrect syntax near 'DaysStay'.

 

[Jamfool]

what happens when u change the set to a SELECT

 

[gmmastros]

If I understand correctly, you want a column in your Bookings table to represent the number of days that a room was booked. Furthermore, you want this column to automatically calculate whenever the table is updated or records are inserted.

Instead of using triggers, you could created a computed column instead.

Alter Table Bookings Add NumberOfDays As DateDiff(dd, FromDate, ToDate)

Notice that I changed the field name. I did this so that your original column is not affected. If this is the functionality you want, then you can rename the computed column to DaysStay. I recommend you add a computed column with a different name first to make sure that the results are what you want before you drop the original DaysStay field and re-add with a computed column.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[fredericofonseca]

change the "set" to "select"

and you should probably have
SELECT DaysStay = (SELECT DATEDIFF(dd, FromDate, ToDate) from INSERTED)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[williamu]

This is what I get:

Server: Msg 207, Level 16, State 3, Procedure Booking_Duration, Line 2
Invalid column name 'ToDate'.
Server: Msg 207, Level 16, State 1, Procedure Booking_Duration, Line 2
Invalid column name 'FromDate'.

Using:

CREATE TRIGGER Booking_Duration ON Booking FOR INSERT, UPDATE AS
SELECT DaysStay = (SELECT DATEDIFF(dd, FromDate, ToDate))

Just in case I've got the wrong end of the stick the purpose of this trigger is to determine the number of days a booking lasts for, and I'm making these assumptions:

DaysStay, FromDate and ToDate are within the Booking Table and therefore don't need to be SELECTed. I've looked at various books as well and the syntax and grammer are correct as I read it, but it still doesn't do as I'd expect it.

 

[williamu]

Thanks guys. That's that one solved. It was missing the FROM Inserted Clause but I've opted to go for the Computed Column as it's more elegant.

Have a star guys.