Creating an update trigger on stock. Please...

[fmasin]

Hi,

I would like to write an update trigger for a hiring database but unfortunately, I'm really new to SQL and I don't know how.

When a customer returns a hired item, the return date is recorded within the 'hire' table which has the following references (customer_id and stock_id).
The Stock table has 'Status' as one of the fields.

What I would like to do is write a trigger that updates the stock status of an item from 'On hire' to 'In Store'. I would like this trigger to fire when the 'return_date'is entered in the hire table. How can I do this?

Thanks and regards,

Francis

 

[AWithers]

Try somehting like the following (sorry not syntax checked)

Andy

create trigger update_status_trigger on hire
for UPDATE
as

DECLARE @count_inserted int
DECLARE @count_deleted int
SELECT @count_deleted = Count(*) FROM deleted
SELECT @count_inserted = Count(*) from inserted
IF @count_deleted <> 0 and @count_inserted <> 0
/* Record has been MODIFIED */
BEGIN
UPDATE Stock
SET
Status = 'In Store'
FROM Stock s, Hire h
WHERE s.stockid = h.stockid
and h.returneddate is null

END

 

[fmasin]

Andy,

Thanks so much....I shall test it immediately.

Thanks and regards,

Francis