Solved

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

Posted on 2007-12-06
6
176 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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 …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

730 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