Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2007-12-06
6
Medium Priority
?
179 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 1200 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 300 total points
ID: 20418748
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

972 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