Learn how to a build a cloud-first strategyRegister Now

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

Creating row-level triggers in SQL Server

Hello,

I"m trying to create some row-level triggers in SQL Server, so that when record #2 gets updated in TABLE A, a record gets inserted in TABLE B referencing the row #2 updated in TABLE A.

Not sure of the transact-SQL on this?

Thanks
0
hpsuser
Asked:
hpsuser
  • 2
  • 2
2 Solutions
 
dportasCommented:
Like this for example:

CREATE TRIGGER trg1 ON TableA
AFTER INSERT, UPDATE
AS

 INSERT INTO TableB (x, z)
  SELECT x, z
   FROM inserted ;

GO
0
 
hpsuserAuthor Commented:
dportas,

Thanks, that's definitely works for the inserts, however when I do an update ion one row it records two rows?  Also, how can I set that up with a delete trigger?
0
 
derekkrommCommented:
Delete one:

CREATE TRIGGER trg2 ON TableA
AFTER DELETE
AS

 INSERT INTO TableB (x, z)
  SELECT x, z
   FROM deleted ;

GO

As for the update creating 2 rows, it should only be creating 1. Could you show some example data of where it is creating 2 instead of 1?
0
 
dportasCommented:
Use separate triggers for UPDATE and DELETE. The full trigger syntax and examples are in Books Online.
0
 
hpsuserAuthor Commented:
thanks dportas and derekkromm
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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