• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

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
 
0
nadermik
Asked:
nadermik
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
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 isnull(d.CustomerCode, 0) <> i.CustomerCode
  end
 

Open in new window

0
 
nadermikAuthor Commented:
Hi
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Open in new window

0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now