Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

Trigger on SQL2005 table

I have a customer master table that includes a field for territory that I need to know when it changes.
I was looking at a job or a trigger (for realtime results) that I could create that would write a record to a table whenever the territory field was changed.

As a job that I would run nightly I can match the customer master accounts in the "Customer Master" table to a "Master Changes" table and if the territories don't match write a new entry with the new territory into the "Master Changes" table.  While this would work it only updates every 24 hours or whenever it is run.

Is it possible to create a trigger to do the same in order to acheive near realtime results?  If so what might it look like?

Thanks
0
jdr0606
Asked:
jdr0606
  • 3
  • 3
1 Solution
 
HainKurtSr. System AnalystCommented:
0
 
HainKurtSr. System AnalystCommented:
example trigger (this works for update only, and inserts record whenever there is an update)

deleted table is the old records, inserted is the new updated records... I am checking if there is any change on UserName, and insert those records into audit table...


create trigger trg_customers on customers for update
as
begin
  insert into customers$aud
  select d.CustID, d.UserName, i.UserName, Getdate() from inserted i, deleted d
  where d.CustID=i.CustID and i.UserName<>d.UserName;
end;

Open in new window

0
 
idmedellinCommented:
--Create a table to register changes
CREATE TABLE auditTerrotori(
      [id] [int] NOT NULL,
      [dterritory] [varchar](50) NOT NULL,
      [iterrotory] [varchar](50) NOT NULL
) ON [PRIMARY]

--Create the trigger in customer table
CREATE TRIGGER Tri_territory
   ON  customer
   AFTER UPDATE
AS
BEGIN
      SET NOCOUNT ON;
      insert into auditTerrotori select d.id, d.territory, i.territory from deleted d join inserted i on d.id = i.id
END
GO
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
jdr0606Author Commented:
Almost, however it creates an audit record for any changes to the customer not just the Sales Territory.

I only want to log a change if the Sales Territory field changes.

I have attached the modified trigger that was suggested.

Thanks
--Create a table to register changes
--drop table dynamicsX.dbo.auditTerrotori
CREATE TABLE dynamicsX.dbo.auditTerrotori(
	  [custnmbr] [char](15) NOT NULL,
      [dterritory] [varchar](50) NOT NULL,
      [iterrotory] [varchar](50) NOT NULL
) ON [PRIMARY]

--Create the trigger in customer table
ALTER TRIGGER [dbo].[Tri_territory]
   ON  [Comp].[dbo].[RM00101]
   AFTER UPDATE
AS 
BEGIN
      SET NOCOUNT ON;
      insert into dynamicsX.dbo.auditTerrotori select d.custnmbr, d.salsterr, i.salsterr from deleted d join inserted i on d.custnmbr = i.custnmbr
END

Open in new window

0
 
HainKurtSr. System AnalystCommented:
look at my sample, Line 6

i.UserName<>d.UserName;

you should do the same thing

      insert into dynamicsX.dbo.auditTerrotori select d.custnmbr, d.salsterr, i.salsterr from deleted d join inserted i on d.custnmbr = i.custnmbr and d.salsterr != i.salsterr

0
 
idmedellinCommented:
ALTER TRIGGER [dbo].[Tri_territory]
   ON  [Comp].[dbo].[RM00101]
   AFTER UPDATE
AS
BEGIN
      SET NOCOUNT ON;
      insert into dynamicsX.dbo.auditTerrotori select d.custnmbr, d.salsterr, i.salsterr from deleted d join inserted i on d.custnmbr = i.custnmbr and d.Territory <>  i.Territory
END
0
 
idmedellinCommented:
--Sorry, it is a where clause

ALTER TRIGGER [dbo].[Tri_territory]
   ON  [Comp].[dbo].[RM00101]
   AFTER UPDATE
AS
BEGIN
      SET NOCOUNT ON;
      insert into dynamicsX.dbo.auditTerrotori select d.custnmbr, d.salsterr, i.salsterr
        from deleted d join inserted i
           on d.custnmbr = i.custnmbr
        where d.Territory <>  i.Territory
END
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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