Update Query Question 1

[ADW2005]

To cut a long story short I posted a thread last week to help sort a query - that has been sorted, i'll give a brief desription. We have a system in place that requests room bookings but there was a problem in the way that dates were stored. Info from a web based front end is inserted into a SQL table but problems with the dates allowed duplicate bookings. I created a trigger that will pull all the info from the fields from the original query and amends them to correct the problem. I did this using an update trigger. Problem is now I'm getting 2 rows affected when I should only have 1..

Is there a before update function like in PL/SQL?

 

[SQLBill]

SQL Server doesn't have a BEFORE UPDATE trigger.

Do you still have the duplicate bookings? If so, that's most likely why you are seeing two records affected.

-SQLBill

Posting advice: FAQ481-4875

 

[ADW2005]

The first record insert still does have booking issue in that dates were being stored incorrectly but the second record that is inserted i(after my trigger modifies it) is fine and the data is stored as intended without any errors.

So there are 2 records inserted, the first is wrong, the second is right but my trigger extracts data from the first, modifies it and then inserts it which is why there are 2 records being inserted.

 

[JamesLean]

I think we might need to see your trigger code. It sounds to me like you need to be UPDATING the first inserted row rather than inserting a whole new one.

--James

 

[ADW2005]

The trigger does insert one row the problem is there are 2 update queries.

The first insert comes from the user when they select their booking.

The second insert comes from the trigger on the table as it modifies the table - code as follows;

declare @from_day datetime
declare @id_booking int
declare @id_resource int
declare @id_user int
declare @priority int


SELECT @from_time = from_time,
@to_time = To_Time,
@from_day = From_Day,
@id_booking = ID_Booking,
@id_resource = ID_Resource,
@id_user = ID_User,
@priority = Priority
FROM oldturtle_resourcebooking_bookings

SET @from_time = CONVERT(varchar(8), @from_day, 112) + ' ' + CONVERT(varchar(12), @from_time, 114)

SET @to_time = CONVERT(varchar(8), @from_day, 112) + ' ' + CONVERT(varchar(12), @to_time, 114)

if

(@from_day < @from_time)

begin

insert dbo.oldturtle_resourcebooking_bookings(From_Day, From_Time, To_Time, ID_Booking, ID_Resource, ID_User, Priority )

values(@from_day, @from_time, @to_time, @id_booking+1, @id_resource, @id_user, @priority)

end

 

[ADW2005]

The second insert comes from the trigger on the table as it modifies the table"

Should read;

The second insert comes from the trigger on the table as it modifies the information.

 

[JamesLean]

As I said, you need to get rid of that insert and change it to update the row just inserted. Something like:

SELECT @from_time = from_time,
  @to_time = To_Time,
  @from_day = From_Day,
  @id_booking = ID_Booking,
  @id_resource = ID_Resource,
  @id_user = ID_User,
  @priority = Priority
FROM [COLOR=red]inserted[/color]

SET @from_time = CONVERT(varchar(8), @from_day, 112) + ' ' + CONVERT(varchar(12), @from_time, 114)

SET @to_time = CONVERT(varchar(8), @from_day, 112) + ' ' + CONVERT(varchar(12), @to_time, 114)

IF @from_day < @from_time
  UPDATE oldturtle_resourcebooking_bookings
  SET from_time = @from_time,
    to_time = @to_time
  FROM oldturtle_resourcebooking_bookings o JOIN inserted i ON o.id_booking = i.id_booking

This assumes id_booking is the primary key on the oldturtle_resourcebooking_bookings table.

--James

 

[ADW2005]

Cool - now I'm with you cheers for that! (Noobs eh!)

 

[ADW2005]

That works - only 1 record is updated and the table is storing data as it should but I'm getting the following?

(1 row(s) affected)


(1 row(s) affected)

 

[JamesLean]

Yes - first is the initial insert, second is the update.

--James

 

[ADW2005]

If you suddenley heard a loud bang that was the penny dropping!

Cheers James