nadermik
asked on
Create a Trigger in SQL
Hi
I have one table (Customers) with the following details
Customercode, Name, Address, Phone
another table Requests
ReqID, ReqDescription, CustomerCode, Freefield1, Freefield2
I need to create a trigger in Microsoft SQL 2005 that when a new record is created or updated in Requests Table the following actions updated
Requests.Freefield1 is updated with the value Customers.Address
Requests.Freefield2 is updated with the value Customers.Phone
Can You Help me creating this trigger
I have one table (Customers) with the following details
Customercode, Name, Address, Phone
another table Requests
ReqID, ReqDescription, CustomerCode, Freefield1, Freefield2
I need to create a trigger in Microsoft SQL 2005 that when a new record is created or updated in Requests Table the following actions updated
Requests.Freefield1 is updated with the value Customers.Address
Requests.Freefield2 is updated with the value Customers.Phone
Can You Help me creating this trigger
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ok, let's fix that:
create trigger trg_requests_get_address
on Requests
for insert, update
as
if @@rowcount > 0
begin
update r
set r.FreeField1 = c.Address
, r.FreeField2 = c.Phone
from Requests r
join Customers c
on c.CustomerCode = r.CustomerCode
join inserted i
on i.ReqID = r.ReqID
left join deleted d
on d.ReqID = i.ReqID
where ( d.CustomerCode IS NULL AND i.CustomerCode IS NOT NULL )
or d.CustomerCode <> i.CustomerCode
end
ASKER
I am getting the following error when running the trigger
Msg 206, Level 16, State 2, Procedure trg_requests_get_address, Line 7
Operand type clash: int is incompatible with uniqueidentifier
Please note that the field r.customercode is varchar and the field c.customercode is char field
can you please help me further