Copying Records into a different table using triggers

I am in the process of writing a trigger that will insert a new record into
another table when a record is added to a specific table. I was wondering if
there is a way to just 'copy' the entire row without using INSERT INTO
...and VALUES?
adv-thanks-anceYou could use
INSERT INTO [AnotherTable]
SELECT * FROM inserted
assuming that [AnotherTable] has the same number of columns, in the same
order, with the same datatypes as the table that has the trigger on it.
"CSHARPITPRO" wrote:

> I am in the process of writing a trigger that will insert a new record int
o
> another table when a record is added to a specific table. I was wondering
if
> there is a way to just 'copy' the entire row without using INSERT INTO
> ...and VALUES?
> adv-thanks-ance|||Thanks Mark,
Is this being copied from the 'INSERTED' table in SQL?
"Mark Williams" wrote:
> You could use
> INSERT INTO [AnotherTable]
> SELECT * FROM inserted
> assuming that [AnotherTable] has the same number of columns, in the same
> order, with the same datatypes as the table that has the trigger on it.
> --
> "CSHARPITPRO" wrote:
>|||I am not sure if there is a way to do this (I have not seen one) but I would
avoid this like the plague.
The tables are identical today, but who knows what will change tomorrow.
Something as simple as adding and audit or archive date to either table
would break any app using such an approach. Structures have a tendency to
evolve over time, and any code that assumes two structures will be identical
is begging for trouble down the line.
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:0078FFA9-4BEB-4A92-9949-CA8B733E7B63@.microsoft.com...
> I am in the process of writing a trigger that will insert a new record
into
> another table when a record is added to a specific table. I was wondering
if
> there is a way to just 'copy' the entire row without using INSERT INTO
> ...and VALUES?
> adv-thanks-ance|||Yes. If all you want to do is a straight copy of the rows inserted into the
table with the trigger on it, you can use the syntax
INSERT INTO [AnotherTable] SELECT * FROM inserted
inserted is the virtual table that conains the inserted rows that caused the
trigger to fire. Note that inserted will also contain rows when an UPDATE is
performed (The new values will be in the inserted virtual table, and the old
values will be in the deleted virtual table).
--
If"CSHARPITPRO" wrote:
> Thanks Mark,
> Is this being copied from the 'INSERTED' table in SQL?
> "Mark Williams" wrote:
>|||Thanks Jim,
What do you think the best method is to copy the 'changed' records to
my AuditTable with out using the INSERTED Table? Should I just use INSERT
INTO ..and VALUES?
Thanks
"Jim Underwood" wrote:

> I am not sure if there is a way to do this (I have not seen one) but I wou
ld
> avoid this like the plague.
> The tables are identical today, but who knows what will change tomorrow.
> Something as simple as adding and audit or archive date to either table
> would break any app using such an approach. Structures have a tendency to
> evolve over time, and any code that assumes two structures will be identic
al
> is begging for trouble down the line.
>
>
> "CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
> news:0078FFA9-4BEB-4A92-9949-CA8B733E7B63@.microsoft.com...
> into
> if
>
>|||I would use insert into with a specific list of values.
i.e. Insert into table1 (field1, field2, field3) values (select field1,
field2, field3 from inserted)
As everyone else mentioned you can use "insert into table select * from
inserted", but if any structures change you will runinto trouble.
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:C1DFF71C-B888-42FC-A6EB-E115C2C25EC0@.microsoft.com...
> Thanks Jim,
> What do you think the best method is to copy the 'changed' records to
> my AuditTable with out using the INSERTED Table? Should I just use INSERT
> INTO ..and VALUES?
> Thanks
> "Jim Underwood" wrote:
>
would
to
identical
wondering|||Yes. Performance and consistency will both be served if you do so. It does
require a bit more work now, and a bit more when you make changes, but if
you design things, this won't be too much of a problem. You can also use
the information_schema.columns view to get the columns that are needed and
build a quicky insert generator (or use the ones in QA/SSMS) to do much of
the hard work of typing out column names.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:C1DFF71C-B888-42FC-A6EB-E115C2C25EC0@.microsoft.com...
> Thanks Jim,
> What do you think the best method is to copy the 'changed' records to
> my AuditTable with out using the INSERTED Table? Should I just use INSERT
> INTO ..and VALUES?
> Thanks
> "Jim Underwood" wrote:
>|||On Wed, 18 Jan 2006 09:23:03 -0800, CSHARPITPRO wrote:

>Thanks Jim,
> What do you think the best method is to copy the 'changed' records to
>my AuditTable with out using the INSERTED Table? Should I just use INSERT
>INTO ..and VALUES?
Hi CSHARPITPRO,
No. INSERT ... VALUES inserts just one row at a time; a trigger can be
fired with a large number of rows in the inserted pseudo-table.
Use
INSERT INTO Tablename (Column1, Column2, ..., ColumnN)
SELECT Column1, Column2, ..., ColumnN
FROM inserted
Hugo Kornelis, SQL Server MVP

Copying deleted into temp table in trigger

For some reason in Enterprise Manager for SQL Server 2000, I cannot
put the following line into a trigger:
select * into #deleted from deleted
When I hit the Apply button I get the following error:
Cannot use text, ntext, or image columns in the 'inserted' or
'deleted' tables

This seems like a weird error, since I am not actually doing anything
to the inserted or deleted tables, I am just trying to make a temp
copy.

I have another workaround but I am just curious why this happens.

Thanks,
RebeccaRebecca Lovelace (usagikawai@.yahoo.com) writes:
> For some reason in Enterprise Manager for SQL Server 2000, I cannot
> put the following line into a trigger:
> select * into #deleted from deleted
> When I hit the Apply button I get the following error:
> Cannot use text, ntext, or image columns in the 'inserted' or
> 'deleted' tables
> This seems like a weird error, since I am not actually doing anything
> to the inserted or deleted tables, I am just trying to make a temp
> copy.
> I have another workaround but I am just curious why this happens.

The message is very clear: there is a text, image, or ntext column in
your table, and cannot access that column. And since SELECT * implies
all columns, you access that column.

On another note, I fond recently that "SELECT * INTO #deleted FROM deleted"
in a trigger can be detrimental to performance. In my case, I was
running a one-by-one processing in a long transaction, and one table
had a trigger with a SELECT INTO like this. I had about given up to
get better speed, when I found that taking out the SELECT INTO and
using "inserted" directly gave a tremendous boost,

The reason this was such a winner, was that the locks on the system
tables in tempdb needed for all these temp tables were eating
resources. I also found that SELECT INTO #temp required more locks and
resources than CREATE TABLE #temp did. But there is a better alternative:
table variables, they don't need any tempdb locks at all.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> The message is very clear: there is a text, image, or ntext column in
> your table, and cannot access that column. And since SELECT * implies
> all columns, you access that column.

It would be clear, if there were any of those types of columns in my
table. But there aren't. It's just varchars and ints.

Does this work for you in SQL Server 2000?

It's more of a matter of curiousity at this point, I know this isn't
the best way to go, I just want to know why I can't do it.

Rebecca|||Rebecca Lovelace (usagikawai@.yahoo.com) writes:
>> The message is very clear: there is a text, image, or ntext column in
>> your table, and cannot access that column. And since SELECT * implies
>> all columns, you access that column.
> It would be clear, if there were any of those types of columns in my
> table. But there aren't. It's just varchars and ints.

That sounds very strange. I'm afraid that I don't have any answer. Is
possible for you to produce a script with a CREATE TABLE statement and
a CREATE TRIGGER that demonstrates the problem? In such case, I could
bring it up with Microsoft.

> Does this work for you in SQL Server 2000?

Yes, I have used SELECT * FROM #deleted FROM deleted in triggers with
success.

Well, success and success I have ran into two problems, but I have not
gotten that weird error message you got.

One problem I have mentioned: performance. The other problem may be
worth mentioning too. Just like you I called the temp table #deleted.
But then I had a trigger that updated another table which did the same
thing. This caused problems because when the second trigger was compiled,
#deleted already existed, but with different columns. So *if* you
this kind of thing, don't call the temp tables #inserted and #deleted,
but use some part of the table name to get a unique name.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Copying a column names from one Database to another Database

Hello to everyone,
I am trying to check scripts when copying column names from one Database to
another Database.
If the Trigger is the answer. I don't know how to write the script properly
calling out the Database name.
Please help.
Thanks so much,Use the threepart name:
Select * from Database.Owner.Objectname
HTH, Jens Suessmeyer.|||Jens wrote:
>Use the threepart name:
>Select * from Database.Owner.Objectname
>HTH, Jens Suessmeyer.
Hi Jens,
Thanks for the attention. However I am not sure what does the "owner" means.
In my example, I need to copy the standard cost column from DB01 to standard
cost column of DB02. The table is Item master.
In this the correct syntax:
Update DB02.dbo(just guessing).stdost
Set DB02.dbo.stdcost=db1.dbo.stdcost
where dbo2.item no. = dbo1.item no.
Please let me if the above is right, else would like to request if you can
correct the above, many thanks!|||Close:
Update DB02.dbo.<tableName>.stdost
Set DB02.dbo.<tableName>.stdcost=db1.dbo.<tableName>.stdcost
where DB02.dbo.<tableName>.item_no. = DB01.dbo.DB02.item_no.
Assuming that the owner is dbo (if not just leave it out -->
DB02..<tableName>.stdost) and the database is on the local server
otherwise you need a linked server entry.
HTH, jens Suessmeyer.|||Jens wrote:
>Close:
>Update DB02.dbo.<tableName>.stdost
>Set DB02.dbo.<tableName>.stdcost=db1.dbo.<tableName>.stdcost
>where DB02.dbo.<tableName>.item_no. = DB01.dbo.DB02.item_no.
>Assuming that the owner is dbo (if not just leave it out -->
>DB02..<tableName>.stdost) and the database is on the local server
>otherwise you need a linked server entry.
>HTH, jens Suessmeyer.
Hi Jens
Thanks so much for your support,
May I further ask if I still using triggers? or the below is the whole synta
x
already?
Also I have question on DB01 of the 3rd line, please see below
Update DB02.dbo.<tableName>.stdost
>Set DB02.dbo.<tableName>.stdcost=db1.dbo.<tableName>.stdcost
>where DB02.dbo.<tableName>.item_no. = DB01.dbo."Should this be <tablename> instead
of DB02".item_no.
At any rate, kindly correct me. I hope I have the last questions on this
matter.
What a great world is this, thanks so much Jens God Bless
>
Message posted via http://www.webservertalk.com|||Sure, just a copy&paste error, sorry for that ;-)
But at the end you learned the syntax that fast, that you could even
find the errors :-)
HTH, Jens Suessmeyer.|||Jens wrote:
>Sure, just a copy&paste error, sorry for that ;-)
>But at the end you learned the syntax that fast, that you could even
>find the errors :-)
>HTH, Jens Suessmeyer.
I have you as my good tutor that's why, :-) Thanks so much ^.^
Message posted via http://www.webservertalk.com

Copy triggers between databases without copying table

Is there any way to copy a trigger between SQL Server databases without
copying the table? In other words, is there some what to apply a
trigger to a table through a script?SMC wrote:
> Is there any way to copy a trigger between SQL Server databases without
> copying the table? In other words, is there some what to apply a
> trigger to a table through a script?
>
Just dump the trigger to a script file on one server, then run that
script on the second server. You should be creating your triggers using
script in the first place, and archiving those scripts off into some
version control software.|||script the triggers
--
thanks,
joey
"SMC" wrote:

> Is there any way to copy a trigger between SQL Server databases without
> copying the table? In other words, is there some what to apply a
> trigger to a table through a script?
>|||Tracy,
Thanks for the response!
I do write all my triggers as a script, but how do you "run that script
on the second server"? Is there a link that could show me how to do
this?
Shane
Tracy McKibben wrote:
> SMC wrote:
> Just dump the trigger to a script file on one server, then run that
> script on the second server. You should be creating your triggers using
> script in the first place, and archiving those scripts off into some
> version control software.|||I figured it out. I can just run it in Query Analyzer.
Thanks for all the help!
SMC wrote:
> Tracy,
> Thanks for the response!
> I do write all my triggers as a script, but how do you "run that script
> on the second server"? Is there a link that could show me how to do
> this?
> Shane
> Tracy McKibben wrote:|||SMC wrote:
> Tracy,
> Thanks for the response!
> I do write all my triggers as a script, but how do you "run that script
> on the second server"? Is there a link that could show me how to do
> this?
>
Same way you run them on your first server - connect with Query
Analyzer, select the proper database, and run the CREATE TRIGGER script.

Copy row in trigger instead of update?

I think this is a tough one, at least with my limited knowlege. Here
is what I am trying to do.
Background:
I want to create an audit trail for edits made to a database. I want
to do this by catching the update in a SQL trigger and turning it into
an insert with a time stamp and cancel the update.
So any row you change really does not change, it is copied to a new
row in the same table with a time stamp.
Copying the row is normally easy as in:
Insert into mytable select * from mytable where id= '17'
This will not work though, The problem with this is that id is an
Identity field and you cannot specify the value of an identity. So the
first trick is to basically do a select * (except id). I figured this
out here is the code:
_________________________________________________________________
declare @.tb sysname, @.col sysname, @.identid sysname
select @.tb='testupdate',
@.col='id'
declare @.sql varchar(2000)
select @.sql=isnull(@.sql,'select ')+quotename(column_name)+','
from information_schema.columns
where column_name!=@.col
and table_name=@.tb
select @.sql='insert into ' + @.tb + ' ' +
substring(@.sql,1,len(@.sql)-1)+ ' from '+quotename(@.tb) + ' where
id=17'
print @.sql
exec(@.sql)
The problem with this is that it copies every row in the table. I
need to copy just the changed row. The above code does not include the
timestamp part here is that part, and separate section of code that
will be in the same trigger.
UPDATE testupdate
SET testupdate.updatedate = GETDATE()
FROM testupdate INNER JOIN Inserted ON testupdate.id = Inserted.id
This little bit of code effectively creates a time stamp on every
edited row and obviously get the id of the changed row. Now if I could
capture that id and use it in the first bit of code I think I would
have it.
I tried to do something like this
CREATE TRIGGER testtrig
ON testupdate
FOR UPDATE
AS
declare @.tb sysname, @.col sysname, @.myid sysname
select @.tb='testupdate',
@.col='id',
@.myid = @.@.identity
declare @.sql varchar(2000)
select @.sql=isnull(@.sql,'select ')+quotename(column_name)+','
from information_schema.columns
where column_name!=@.col
and table_name=@.tb
select @.sql='insert into ' + @.tb + ' ' +
substring(@.sql,1,len(@.sql)-1)+ ' from '+quotename(@.tb) + ' where id='
+ @.myid
print @.sql
exec(@.sql)
UPDATE testupdate
SET testupdate.updatedate = GETDATE()
FROM testupdate INNER JOIN Inserted ON testupdate.id = Inserted.idThere are several things you might consider.
If you can, add a column to the table with a default value of getdate, so
all inserts are covered.
Then read about instead of triggers... They are new to SQL 2000, the normal
trigger fires AFTER the action, which is causing you a problem. An instead
of trigger fires INSTEAD OF the update ( for instance). You still get the
inserted ,deleted tables. The ACTUAL update only occurs IF you include an
update in your trigger... you could write something like
create trigger mytrig on mytable instead of update
as
insert into mytable select * from inserted
The original update would NOT occur, and instead you would have inserted the
NEW value of the row. The NEW row would have a new ID of course... you'd
have to mess with that...perhaps have a second integer column which
contains the original ID..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"OrlandoRocks" <parkJunkie@.orlandorocks.com> wrote in message
news:55879c92.0409120419.8418a63@.posting.google.com...
> I think this is a tough one, at least with my limited knowlege. Here
> is what I am trying to do.
> Background:
> I want to create an audit trail for edits made to a database. I want
> to do this by catching the update in a SQL trigger and turning it into
> an insert with a time stamp and cancel the update.
> So any row you change really does not change, it is copied to a new
> row in the same table with a time stamp.
> Copying the row is normally easy as in:
> Insert into mytable select * from mytable where id= '17'
> This will not work though, The problem with this is that id is an
> Identity field and you cannot specify the value of an identity. So the
> first trick is to basically do a select * (except id). I figured this
> out here is the code:
> _________________________________________________________________
> declare @.tb sysname, @.col sysname, @.identid sysname
> select @.tb='testupdate',
> @.col='id'
> declare @.sql varchar(2000)
> select @.sql=isnull(@.sql,'select ')+quotename(column_name)+','
> from information_schema.columns
> where column_name!=@.col
> and table_name=@.tb
> select @.sql='insert into ' + @.tb + ' ' +
> substring(@.sql,1,len(@.sql)-1)+ ' from '+quotename(@.tb) + ' where
> id=17'
> print @.sql
> exec(@.sql)
>
> The problem with this is that it copies every row in the table. I
> need to copy just the changed row. The above code does not include the
> timestamp part here is that part, and separate section of code that
> will be in the same trigger.
> UPDATE testupdate
> SET testupdate.updatedate = GETDATE()
> FROM testupdate INNER JOIN Inserted ON testupdate.id => Inserted.id
>
> This little bit of code effectively creates a time stamp on every
> edited row and obviously get the id of the changed row. Now if I could
> capture that id and use it in the first bit of code I think I would
> have it.
> I tried to do something like this
>
> CREATE TRIGGER testtrig
> ON testupdate
> FOR UPDATE
> AS
> declare @.tb sysname, @.col sysname, @.myid sysname
> select @.tb='testupdate',
> @.col='id',
> @.myid = @.@.identity
> declare @.sql varchar(2000)
> select @.sql=isnull(@.sql,'select ')+quotename(column_name)+','
> from information_schema.columns
> where column_name!=@.col
> and table_name=@.tb
> select @.sql='insert into ' + @.tb + ' ' +
> substring(@.sql,1,len(@.sql)-1)+ ' from '+quotename(@.tb) + ' where id='
> + @.myid
> print @.sql
> exec(@.sql)
> UPDATE testupdate
> SET testupdate.updatedate = GETDATE()
> FROM testupdate INNER JOIN Inserted ON testupdate.id => Inserted.id