Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-12-06
6
Medium Priority
?
178 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

722 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