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...
>
>

DELETE rows involving auto-increment fields

I tried to delete a couple of records from a table which has a ID field
which is auto-incremented(IDENTITY) . But, after deletion... when I
tried to insert new records, the ID field gets incremented from
previous next value (before deletion). Is there any way to get around
this? Plz helpLookup DBCC CHECKIDENT
example:
Force the current identity value to 30
This example forces the current identity value in the jobs table to a
value of 30.
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
http://sqlservercode.blogspot.com/|||"juventus" <saurabh.kotkar@.gmail.com> wrote in message
news:1140806282.998562.62590@.j33g2000cwa.googlegroups.com...
>I tried to delete a couple of records from a table which has a ID field
> which is auto-incremented(IDENTITY) . But, after deletion... when I
> tried to insert new records, the ID field gets incremented from
> previous next value (before deletion). Is there any way to get around
> this? Plz help
>
First of all, a lot of things can happen that may cause gaps in Identity.
You have found one. Rolled back transactions are another.
SQL only guarantees that the values will be incremental.
See DBCC Checkident in Books On-Line.|||Thanks a lot man..! It worked. I was able to reseed the increment
value..|||Confirm that you have a unique index or constraint on this column. I think
it may be possible for SQL Server to repeat sequences of identity numbers if
the maximum value for data type is reached or as a result of reseeding to a
value less than the current highest value.
"juventus" <saurabh.kotkar@.gmail.com> wrote in message
news:1140808374.186842.221090@.u72g2000cwu.googlegroups.com...
> Thanks a lot man..! It worked. I was able to reseed the increment
> value..
>

DELETE problem

I have a hierarchy of menu and sub menus setup using this table:
CREATE TABLE menu
(
id INT NOT NULL IDENTITY PRIMARY KEY,
name VARCHAR(30)NOT NULL,
parentID INTNULL --Null If Root Menu
)

I want all sub menus to be deleted when a parent menu is deleted. I
wasn't able to get a recursive procedure to work because I got an error
about multiple cursors w/ the same name.

The best I've come up w/ is this:

DELETE FROM menu WHERE id = x --Del Menu
--Cleanup Children
DELETE FROM menu WHERE parentID <> NULL AND parentID NOT IN(SELECT id
from menu)

Is there a better/faster way of doing this?Hi

If you did not see my reply to your previous post, the following is about
the best you can do

DELETE FROM menu WHERE id = x --Del Menu

WHILE @.@.ROWCOUNT > 0
BEGIN
DELETE FROM menu
WHERE parentID IS NOT NULL
AND parentID NOT IN (SELECT id FROM menu)
END

Do not use <> NULL

John

<wackyphill@.yahoo.com> wrote in message
news:1103306757.201621.73930@.z14g2000cwz.googlegro ups.com...
>I have a hierarchy of menu and sub menus setup using this table:
> CREATE TABLE menu
> (
> id INT NOT NULL IDENTITY PRIMARY KEY,
> name VARCHAR(30) NOT NULL,
> parentID INT NULL --Null If Root Menu
> )
> I want all sub menus to be deleted when a parent menu is deleted. I
> wasn't able to get a recursive procedure to work because I got an error
> about multiple cursors w/ the same name.
> The best I've come up w/ is this:
> DELETE FROM menu WHERE id = x --Del Menu
> --Cleanup Children
> DELETE FROM menu WHERE parentID <> NULL AND parentID NOT IN(SELECT id
> from menu)
> Is there a better/faster way of doing this?|||John Bell wrote:
> Hi
> If you did not see my reply to your previous post, the following is
about
> the best you can do
> DELETE FROM menu WHERE id = x --Del Menu
> WHILE @.@.ROWCOUNT > 0
> BEGIN
> DELETE FROM menu
> WHERE parentID IS NOT NULL
> AND parentID NOT IN (SELECT id FROM menu)
> END
> Do not use <> NULL
> John
> <wackyphill@.yahoo.com> wrote in message
> news:1103306757.201621.73930@.z14g2000cwz.googlegro ups.com...
> >I have a hierarchy of menu and sub menus setup using this table:
> > CREATE TABLE menu
> > (
> > id INT NOT NULL IDENTITY PRIMARY KEY,
> > name VARCHAR(30) NOT NULL,
> > parentID INT NULL --Null If Root Menu
> > )
> > I want all sub menus to be deleted when a parent menu is deleted. I
> > wasn't able to get a recursive procedure to work because I got an
error
> > about multiple cursors w/ the same name.
> > The best I've come up w/ is this:
> > DELETE FROM menu WHERE id = x --Del Menu
> > --Cleanup Children
> > DELETE FROM menu WHERE parentID <> NULL AND parentID NOT IN(SELECT
id
> > from menu)
> > Is there a better/faster way of doing this?|||Thanks, John. That works great!|||Sorry John I did miss your last post and you are right, that works very
well. Thank you very much.|||Look up the nested sets model for trees; this can be done without
procedural code.