February 19, 2016 at 7:17 am
I've following trigger to "move" record just updated on Data_Canc to a shadow copy of same table.
The problem is that original table have an identity column and when the trigger is fired i've this error :"An explicit value for identity column in table dbo.N8_SPEC_STAT_DELETED can only be specified when a column list is used and IDENTITY_INSERT is ON"
CREATE TRIGGER TRG_N8_SPEC_STAT_PREVENT_CANCEL ON dbo.N8_SPEC_STAT
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE(DATA_CANC)
BEGIN
DELETE N
FROM dbo.N8_SPEC_STAT N
INNER JOIN Inserted i ON i.CODI = N.CODI;
IF NOT EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[N8_SPEC_STAT_DELETED]')
AND type IN ( N'U' ) )
BEGIN
SELECT *
INTO dbo.[N8_SPEC_STAT_DELETED]
FROM Inserted;
END;
ELSE
BEGIN
SET IDENTITY_INSERT dbo.[N8_SPEC_STAT_DELETED] ON;
INSERT INTO dbo.[N8_SPEC_STAT_DELETED]
SELECT *
FROM Inserted;
SET IDENTITY_INSERT dbo.[N8_SPEC_STAT_DELETED] OFF;
END;
END;
END;
As you can see I've also tried to put identity_inser in the trigger without success.
Any help?
February 19, 2016 at 7:34 am
You have a couple of logical concerns in your trigger. The first one is the one that is causing you issues. Why do you need to create the table in your trigger? Why not create the table ahead of time? This will make your trigger much simpler, AND it lets you define the Deleted table without an identity.
The other concern here is that you are checking if the column DATA_CANC is updated. This means if that column is in the update statement this trigger logic is going to fire, even if the value is the same. I would recommend using OUTPUT for this instead of all these gyrations.
Assuming you create the N8_SPEC_STAT_DELETED ahead of time table your trigger code could be simplified to this.
CREATE TRIGGER TRG_N8_SPEC_STAT_PREVENT_CANCEL ON dbo.N8_SPEC_STAT
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
DELETE N
OUTPUT i.* into N8_SPEC_STAT_DELETED
FROM dbo.N8_SPEC_STAT N
INNER JOIN Inserted i ON i.CODI = N.CODI
INNER JOIN deleted d on d.CODI = N.CODI
where i.DATA_CANC <> d.DATA_CANC;
END;
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 19, 2016 at 7:35 am
The cause is detailed in the error message.
"An explicit value for identity column in table dbo.N8_SPEC_STAT_DELETED can only be specified when a column list is used and IDENTITY_INSERT is ON"
You don't have a column list specified for the insert.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 19, 2016 at 7:59 am
GilaMonster (2/19/2016)
The cause is detailed in the error message."An explicit value for identity column in table dbo.N8_SPEC_STAT_DELETED can only be specified when a column list is used and IDENTITY_INSERT is ON"
You don't have a column list specified for the insert.
even including ALL the columns instead of * (identity column included) i've same error
February 19, 2016 at 8:21 am
LittleTony (2/19/2016)
GilaMonster (2/19/2016)
The cause is detailed in the error message."An explicit value for identity column in table dbo.N8_SPEC_STAT_DELETED can only be specified when a column list is used and IDENTITY_INSERT is ON"
You don't have a column list specified for the insert.
even including ALL the columns instead of * (identity column included) i've same error
Did you look at my response? It would eliminate this problem and make your code a LOT simpler.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 19, 2016 at 8:31 am
Sean Lange (2/19/2016)
You have a couple of logical concerns in your trigger. The first one is the one that is causing you issues. Why do you need to create the table in your trigger? Why not create the table ahead of time? This will make your trigger much simpler, AND it lets you define the Deleted table without an identity.The other concern here is that you are checking if the column DATA_CANC is updated. This means if that column is in the update statement this trigger logic is going to fire, even if the value is the same. I would recommend using OUTPUT for this instead of all these gyrations.
Assuming you create the N8_SPEC_STAT_DELETED ahead of time table your trigger code could be simplified to this.
CREATE TRIGGER TRG_N8_SPEC_STAT_PREVENT_CANCEL ON dbo.N8_SPEC_STAT
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
DELETE N
OUTPUT i.* into N8_SPEC_STAT_DELETED
FROM dbo.N8_SPEC_STAT N
INNER JOIN Inserted i ON i.CODI = N.CODI
INNER JOIN deleted d on d.CODI = N.CODI
where i.DATA_CANC <> d.DATA_CANC;
END;
The reason is i've more then 1000 tables and i need a shadow copy for most of all so i'd like to semplify and automate this process.
I'd want to create a "common" trigger valid for all the table so if the table doesn't exist i need to create it "on fly".
In our scenario Data_canc with a value means "soft deletion" and i want to put "deleted" record in other table but i should skip (or change) identity column.
February 19, 2016 at 8:44 am
LittleTony (2/19/2016)
Sean Lange (2/19/2016)
You have a couple of logical concerns in your trigger. The first one is the one that is causing you issues. Why do you need to create the table in your trigger? Why not create the table ahead of time? This will make your trigger much simpler, AND it lets you define the Deleted table without an identity.The other concern here is that you are checking if the column DATA_CANC is updated. This means if that column is in the update statement this trigger logic is going to fire, even if the value is the same. I would recommend using OUTPUT for this instead of all these gyrations.
Assuming you create the N8_SPEC_STAT_DELETED ahead of time table your trigger code could be simplified to this.
CREATE TRIGGER TRG_N8_SPEC_STAT_PREVENT_CANCEL ON dbo.N8_SPEC_STAT
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
DELETE N
OUTPUT i.* into N8_SPEC_STAT_DELETED
FROM dbo.N8_SPEC_STAT N
INNER JOIN Inserted i ON i.CODI = N.CODI
INNER JOIN deleted d on d.CODI = N.CODI
where i.DATA_CANC <> d.DATA_CANC;
END;
The reason is i've more then 1000 tables and i need a shadow copy for most of all so i'd like to semplify and automate this process.
I'd want to create a "common" trigger valid for all the table so if the table doesn't exist i need to create it "on fly".
In our scenario Data_canc with a value means "soft deletion" and i want to put "deleted" record in other table but i should skip (or change) identity column.
But your code isn't generic anyway. It is tightly coupled to the base table. This only simplifies the process for the developer. Overall this is going to add a lot of overhead to the system that isn't needed. You can easily script all of your tables at once and quickly modify them to remove identity and change the name to your audit format. In the end it is up to you but this sounds like a lazy approach from a coding standpoint.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 19, 2016 at 9:08 am
Sean Lange (2/19/2016)
LittleTony (2/19/2016)
Sean Lange (2/19/2016)
You have a couple of logical concerns in your trigger. The first one is the one that is causing you issues. Why do you need to create the table in your trigger? Why not create the table ahead of time? This will make your trigger much simpler, AND it lets you define the Deleted table without an identity.The other concern here is that you are checking if the column DATA_CANC is updated. This means if that column is in the update statement this trigger logic is going to fire, even if the value is the same. I would recommend using OUTPUT for this instead of all these gyrations.
Assuming you create the N8_SPEC_STAT_DELETED ahead of time table your trigger code could be simplified to this.
CREATE TRIGGER TRG_N8_SPEC_STAT_PREVENT_CANCEL ON dbo.N8_SPEC_STAT
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
DELETE N
OUTPUT i.* into N8_SPEC_STAT_DELETED
FROM dbo.N8_SPEC_STAT N
INNER JOIN Inserted i ON i.CODI = N.CODI
INNER JOIN deleted d on d.CODI = N.CODI
where i.DATA_CANC <> d.DATA_CANC;
END;
The reason is i've more then 1000 tables and i need a shadow copy for most of all so i'd like to semplify and automate this process.
I'd want to create a "common" trigger valid for all the table so if the table doesn't exist i need to create it "on fly".
In our scenario Data_canc with a value means "soft deletion" and i want to put "deleted" record in other table but i should skip (or change) identity column.
But your code isn't generic anyway. It is tightly coupled to the base table. This only simplifies the process for the developer. Overall this is going to add a lot of overhead to the system that isn't needed. You can easily script all of your tables at once and quickly modify them to remove identity and change the name to your audit format. In the end it is up to you but this sounds like a lazy approach from a coding standpoint.
You are right, is not generic yet.
I started from that table to then try to generalize all the process.
Considering the number of tables i thought was better to create the shadow copy (for the first time) in the trigger.
Probably is not the best approch 😉
February 19, 2016 at 9:09 am
GilaMonster (2/19/2016)
The cause is detailed in the error message."An explicit value for identity column in table dbo.N8_SPEC_STAT_DELETED can only be specified when a column list is used and IDENTITY_INSERT is ON"
You don't have a column list specified for the insert.
That's because it's not the * that is causing the problem, it is the missing column list on the INSERT INTO.
Try INSERT INTO TableName (columns, column, ...) SELECT ...
February 19, 2016 at 9:11 am
LittleTony (2/19/2016)
Probably is not the best approch 😉
It's definitely not the best approach.
If you must do this, then use the system tables (sys.tables, sys.columns, etc) to generate CREATE TABLE scripts for all your audit tables, and generate the triggers for all of them.
Or, much better, use some off-the-shelf auditing solution for this.
February 19, 2016 at 11:23 am
LittleTony (2/19/2016)
GilaMonster (2/19/2016)
The cause is detailed in the error message."An explicit value for identity column in table dbo.N8_SPEC_STAT_DELETED can only be specified when a column list is used and IDENTITY_INSERT is ON"
You don't have a column list specified for the insert.
even including ALL the columns instead of * (identity column included) i've same error
You need to specify the column list for the INSERT, not the SELECT
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply