Solved

Create a Trigger in SQL

Posted on 2010-11-30
3
297 Views
Last Modified: 2012-06-21
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
Comment
Question by:nadermik
  • 2
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
 

Author Comment

by:nadermik
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now