deleted object for trigger

Is 'deleted' object available for a table with Identity field? I try the
scripts as following. It gets me the error "Invalid object name 'deleted'".
How can I bypass it? Thanks.
CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
FOR UPDATE
AS
SET IDENTITY_INSERT dbo.myTable_History ON
GO
INSERT dbo.myTable_History SELECT * FROM deleted
GO
SET IDENTITY_INSERT dbo.myTable_History OFF
GOGO terminates batches in Query Analyzer. So your trigger does nothing
more than SET IDENTITY_INSERT ON for the table. Remove the GOs and it
should work...
That said, why does your table have an IDENTITY column if you're just
bypassing it from the trigger anyway?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:83E83E75-16E6-4473-B3AB-BEA946A60B08@.microsoft.com...
> Is 'deleted' object available for a table with Identity field? I try the
> scripts as following. It gets me the error "Invalid object name
'deleted'".
> How can I bypass it? Thanks.
> CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
> FOR UPDATE
> AS
> SET IDENTITY_INSERT dbo.myTable_History ON
> GO
> INSERT dbo.myTable_History SELECT * FROM deleted
> GO
> SET IDENTITY_INSERT dbo.myTable_History OFF
> GO
>|||The GO keyword should be only at the end of the trigger, not after each
statement.
To use SET IDENTITY_INSERT you must specify the columns (it doesn't
work with *)
Razvan|||1.
I removed all GO statement. Now the trigger is:
CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
FOR UPDATE
AS
SET IDENTITY_INSERT dbo.myTable_History ON
INSERT dbo.myTable_History SELECT * FROM deleted
SET IDENTITY_INSERT dbo.myTable_History OFF
It still doesn't work though. The error shows:
Error 8101: An explicit value for the identity column in ... can only be
specified when a columne list is used and IDENTITY_INSERT is ON.
Do I miss anything?
2.
You raised a good quesiton. The reason I have IDENTITY field on the history
tables is just because they are created in the SQL script by
SELECT * INTO MyTable1_History FROM MyTable1
SELECT * INTO MyTable2_History FROM MyTable2
............
Since there is no short cut to change table's IDENTITY field to be plain int
field, we keep the IDENTITY field in the history table.
"Adam Machanic" wrote:

> GO terminates batches in Query Analyzer. So your trigger does nothing
> more than SET IDENTITY_INSERT ON for the table. Remove the GOs and it
> should work...
> That said, why does your table have an IDENTITY column if you're just
> bypassing it from the trigger anyway?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Sean" <Sean@.discussions.microsoft.com> wrote in message
> news:83E83E75-16E6-4473-B3AB-BEA946A60B08@.microsoft.com...
> 'deleted'".
>
>|||Thanks for your reply. What do you mean 'To use SET IDENTITY_INSERT you must
specify the columns'. Shouldn't I SET IDENTITY_INSERT ON to the table?
"Razvan Socol" wrote:

> The GO keyword should be only at the end of the trigger, not after each
> statement.
> To use SET IDENTITY_INSERT you must specify the columns (it doesn't
> work with *)
> Razvan
>|||A) Use a column list
B) Stop being lazy and create your tables using Data Definition Language.
Why would you take a shortcut that doesn't save much time and is going to
make your database worse?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:BBCE9EF9-1D63-4947-9B38-3A2F96195A3C@.microsoft.com...
> 1.
> I removed all GO statement. Now the trigger is:
> CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
> FOR UPDATE
> AS
> SET IDENTITY_INSERT dbo.myTable_History ON
> INSERT dbo.myTable_History SELECT * FROM deleted
> SET IDENTITY_INSERT dbo.myTable_History OFF
> It still doesn't work though. The error shows:
> Error 8101: An explicit value for the identity column in ... can only be
> specified when a columne list is used and IDENTITY_INSERT is ON.
> Do I miss anything?
> 2.
> You raised a good quesiton. The reason I have IDENTITY field on the
history
> tables is just because they are created in the SQL script by
> SELECT * INTO MyTable1_History FROM MyTable1
> SELECT * INTO MyTable2_History FROM MyTable2
> ............
> Since there is no short cut to change table's IDENTITY field to be plain
int
> field, we keep the IDENTITY field in the history table.
>
> "Adam Machanic" wrote:
>
nothing
just
the|||You have to write the column list.
CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
FOR UPDATE
AS
SET IDENTITY_INSERT dbo.myTable_History ON
INSERT dbo.myTable_History (col1, ..., coln)
SELECT col1,..., coln FROM deleted
SET IDENTITY_INSERT dbo.myTable_History OFF
go

> You raised a good quesiton. The reason I have IDENTITY field on the histor
y
> tables is just because they are created in the SQL script by
> SELECT * INTO MyTable1_History FROM MyTable1
> SELECT * INTO MyTable2_History FROM MyTable2
select col2, ..., coln
into t
from table1
alter table t
add col1 int not nul
go
AMB
"Sean" wrote:
> 1.
> I removed all GO statement. Now the trigger is:
> CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
> FOR UPDATE
> AS
> SET IDENTITY_INSERT dbo.myTable_History ON
> INSERT dbo.myTable_History SELECT * FROM deleted
> SET IDENTITY_INSERT dbo.myTable_History OFF
> It still doesn't work though. The error shows:
> Error 8101: An explicit value for the identity column in ... can only be
> specified when a columne list is used and IDENTITY_INSERT is ON.
> Do I miss anything?
> 2.
> You raised a good quesiton. The reason I have IDENTITY field on the histor
y
> tables is just because they are created in the SQL script by
> SELECT * INTO MyTable1_History FROM MyTable1
> SELECT * INTO MyTable2_History FROM MyTable2
> ............
> Since there is no short cut to change table's IDENTITY field to be plain i
nt
> field, we keep the IDENTITY field in the history table.
>
> "Adam Machanic" wrote:
>|||Sean
To use INSERT to put rows in a table with INDENTITY_INSERT ON, you must
explicitly list all the columns in the table. Please read about the
variations of the INSERT command in the Books Online.
It would be something like this:
INSERT dbo.myTable_History (name_of_column1, name_of_column_2, ...)
SELECT * FROM deleted
One of the columns names needs to be the name of the identity column, in the
right position.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:3A42CE27-B412-424B-9A12-35882AB63F4F@.microsoft.com...
> Thanks for your reply. What do you mean 'To use SET IDENTITY_INSERT you
> must
> specify the columns'. Shouldn't I SET IDENTITY_INSERT ON to the table?
>
> "Razvan Socol" wrote:
>|||Thanks for the reply. I got it.
"Alejandro Mesa" wrote:
> You have to write the column list.
> CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
> FOR UPDATE
> AS
> SET IDENTITY_INSERT dbo.myTable_History ON
> INSERT dbo.myTable_History (col1, ..., coln)
> SELECT col1,..., coln FROM deleted
> SET IDENTITY_INSERT dbo.myTable_History OFF
> go
>
> select col2, ..., coln
> into t
> from table1
> alter table t
> add col1 int not nul
> go
>
> AMB
> "Sean" wrote:
>|||Thanks, Kalen.
"Kalen Delaney" wrote:

> Sean
> To use INSERT to put rows in a table with INDENTITY_INSERT ON, you must
> explicitly list all the columns in the table. Please read about the
> variations of the INSERT command in the Books Online.
> It would be something like this:
> INSERT dbo.myTable_History (name_of_column1, name_of_column_2, ...)
> SELECT * FROM deleted
> One of the columns names needs to be the name of the identity column, in t
he
> right position.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Sean" <Sean@.discussions.microsoft.com> wrote in message
> news:3A42CE27-B412-424B-9A12-35882AB63F4F@.microsoft.com...
>
>