Solved

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

Posted on 2007-12-06
6
174 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to automate & schedule this Index optimization Script ? 10 77
Truncate vs Delete 63 102
Need help with a query 6 67
My Query is not giving correct result. Please help 5 32
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

864 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

20 Experts available now in Live!

Get 1:1 Help Now