Solved

Insert Triggers - how do i detect the values that were inserted

Posted on 2007-12-06
6
173 Views
Last Modified: 2010-03-19
I'm looking to insert the values inserted in a table onto another server - how do i detect the orgional values - i know of something called magic tables - is this the best way?
CREATE TRIGGER trgInsert

ON TABLE1.DBO.User_Master

FOR INSERT

AS 
 

GO

 

BEGIN TRANSACTION

DECLARE @ERR INT

 

	INSERT INTO TABLE2.DBO.USER_MASTER(USERNAME, PASSWORD)

	VALUES(????????,?????????)
 

SET @ERR = @@Error

IF @ERR = 0    

             BEGIN

                           ROLLBACK TRANSACTION
 

             END

ELSE

             BEGIN

                           COMMIT TRANSACTION

             END

Open in new window

0
Comment
Question by:paulCardiff
  • 2
  • 2
  • 2
6 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20418716
the magic table inserted will contain the new values  and the deleted contain the deleted rows
In sql 2005 there is a new feature for OUTPUTing the modified values to a table
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20418720
They are called INSERTED and DELETED
reference them as you would any table, i.e.
SELECT X, Y from INSERTED
0
 

Author Comment

by:paulCardiff
ID: 20418727
Thanks for the info,

Sorry what is that new feature, and ideally provide any sample syntax if possible?
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:paulCardiff
ID: 20418733
sorry sent reply before seeing imitchies reply -

So is "SELECT X, Y from INSERTED" - is x and y just the standard field names?
0
 
LVL 25

Accepted Solution

by:
imitchie earned 400 total points
ID: 20418744
BEGIN TRANSACTION
DECLARE @ERR INT
 
        INSERT INTO TABLE2.DBO.USER_MASTER(USERNAME, PASSWORD)
        SELECT USER, PASSWD FROM INSERTED -- direct copy
 
SET @ERR = @@Error
IF @ERR = 0    
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 100 total points
ID: 20418748
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

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…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video discusses moving either the default database or any database to a new volume.

757 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

19 Experts available now in Live!

Get 1:1 Help Now