Trigger and procedure

[rahulae]

given a propertyid and sale amount the system should change property status to 'sold'
when it is marked sold askingprice should be added to totalsales.

property(proper ty_id,property_ name,asking_pri ce,status,total _size,address,c ity,state,agent _id)

agent(agent_id, last_name,first _name,start_dat e,contact_no,to tal_sales,prope rty_id)

[r035198x]

Sounds like homework. What have you done so far?

[rahulae]

some how not getting the idea to do it

help me

[rahulae]

procedure

create proc sp_prop_status
(@property_id int,@asking_pri ce float(2))
as
begin
If not exists (select * from property where property_id=@pr operty_id)
begin
select 'not a valid property'
return
end

begin transaction
update property set status = 'sold'
where property_id=@pr operty_id
if @@error <>0 or @@rowcount <> 1
begin
rollback transaction
select 'insert failed'
return
end
commit transaction
end

This is what i have done

[rahulae]

im not able to implement the 2nd part adding asking_price to total_sales when it is marked sold

[rahulae]

delerna or administrator plz help me out

[ck9663]

Looks like you're expecting to update only 1 record with this condition @@rowcount <> 1, otherwise it rolls back.

-- CK

[rahulae]

yes ur right after updating status i should be able to use trigger to update total_sales

[ck9663]

I mean record, not column.

If your table have many rows that will be updated @@rowcount is > 1 hence it rolls back.

If this is a homework, you have to figure out the rest.

-- CK