October 13, 2009 at 12:56 pm
I created a trigger where if a delete happens in schema1.dimcadr table then data which is deleted should go to schema2.dimcadr table and the data should be deleted in schema1.dimcadr table
Trigger is created successfully but only for the first deletion and later it is giving error as IDENTITY_INSERT is already ON for table 'Schema2.dbo.DIMCADR'. Cannot perform SET operation for table 'Schema2.dbo.DIMCADR'
Can anypne help me out what the problem is!!
Here is the code
CREATE TRIGGER DIMCADR_TRG
ON
SCHEMA1.dbo.DIMCADR
FOR DELETE
AS
SET IDENTITY_INSERT SCHEMA2.dbo.DIMCADR ON
INSERT INTO Schema2.dbo.DIMCADR(co1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18, col19, col20, col21, col22, col23, col24, col25, col26, col27, col28, col29)
SELECT
Col1,
Col2,
'P',
Col4,
Col5,
Col6,
Col7,
Col8,
Col9,
Col10,
NULL,
NULL,
NULL,
NULL,
Col15,
Col16,
Col17,
Col18,
Col19,
Col20,
Col21,
Col22,
Col23,
Col24,
Col25,
Col26,
Col27,
Col28,
Col29
FROM DELETED
WHERE col5 = 102782724
SET IDENTITY_INSERT Schema2.dbo.DIMCADR OFF
October 13, 2009 at 7:19 pm
Is there a reason you need that column to be an identity column at all? If you're using it as an identity column for other things, and just manually inserting these specific records as well, what are you going to do if your identity value is already taken? This seems like a really bad idea.
October 13, 2009 at 7:24 pm
OK, I knew this looked familiar. I see from your other thread that you are very new. Please explain as best you can, why you think you need to do this and what your end goal is here. Once we understand what you're really trying to do, we might be able to suggest a different method all together.
October 14, 2009 at 8:33 am
Thanks for taking interest in my problem
Those identity columns are already been set. I dont have an option to change them.
All i have to do is to create a trigger for delete so that data should be deleted form old table and deleted data should go to new table.
I created test data and inserted two rows. While i am inserting i am keeping SET iDENTITY_INSERT ON for both rows.
I even wrote SET IDENTITY_INSERT ON in trigger also otherwise it is givung error. After inserting first row i deleted again and the trigger worked fine. After inserting second row i deleted that too for testing. For the second insert i am getting error that SET IDENTITY_INSERT is already ON for the table Cannot perform SET for table.
This is my problem.
October 14, 2009 at 10:03 am
rahulsony111 (10/14/2009)
Thanks for taking interest in my problemThose identity columns are already been set. I dont have an option to change them.
All i have to do is to create a trigger for delete so that data should be deleted form old table and deleted data should go to new table.
I created test data and inserted two rows. While i am inserting i am keeping SET iDENTITY_INSERT ON for both rows.
I even wrote SET IDENTITY_INSERT ON in trigger also otherwise it is givung error. After inserting first row i deleted again and the trigger worked fine. After inserting second row i deleted that too for testing. For the second insert i am getting error that SET IDENTITY_INSERT is already ON for the table Cannot perform SET for table.
This is my problem.
I don't know that this is you problem. I'm worried that you're overlooking something as far as identity columns. Let me just make a few statements.
1. Identity columns do not *have* to be the primary key for a table, but in most cases they are. If yours is, you will very likely violate primary key constraints trying to insert a value that is already there.
2. If the identity field is not a primary key, it is still likely there to guarantee uniqueness. Inserting manual values in this column would go through, but your data would be corrputed and have trickle down effects to anything else that expected the identity column to maintain unique values.
3. This is almost surely destined to fail if that second table is being inserted into from anything other than this trigger.
4. Identity columns auto-increment automatically and independently. For example, say you start both identity columns at 1. You insert 10 rows into both tables. Now you try to delete row 3 from table 1 and insert it into table 2. You're now either going to get an error (because identity value 3 already exists in table 2) or you're going to have 2 3's in 2, rendering that identity column mostly useless. The second table has already used identity value of 3 on its own because it is only unique in and of itself.
5. If you *are* the only one inserting into this table, then you should have the ability to change the design of the table. If it is a permissions issue, then I advise you talk to the person who does have the permission, because designing something like this that is destined to fail simply because you don't have the permissions to modify the design is a bad move.
6. The entire reason to use an identity column is to let the engine handle the auto-increments. If you have to constantly insert specific values, you should not be using an identity column. There are plenty of other ways to accomplish similar goals.
Does all of that make sense?
October 14, 2009 at 12:22 pm
I will try to explain my issue in detail
I have a table in two schemas.
Schema1.DIMCNAM and Schema2.DIMCNAM
They are connected through SSIS.
Whatever data is in Schema1.DIMCNAM is there in Schema2.DIMCNAM and Schema2.DIMCNAM has some additional data also.
In Schema2.DIMCNAM there is no Primary Key but have a IDENTITY COLUMN that is col1.
In Schema1.DIMCNAM i have Primary Key as well as Identity column.
Whenever a delete happens in Schema1.DIMCNAM the deleted data should be sent to Schema2.DIMCNAM.
The trigger is running fine when the value in col1 which is identity column is different to value in col1 Schema2.DIMCNAM.
But if i delete a value in Schema1.DIMCNAM and if that value already exists in Schema2.DIMCNAM then i am getting error because COl1 value is same both for the Deleted row which is in Schema1.DIMCNAM and for the Schema2.DIMCNAM.
I have only one Identity Column in Schema2.DIMCNAM that is col1 and the problem will be with that only.
The code which i wrote for the trigger inserts that identity column also. ( I am told by my head to do so in my Specificatios). But errors occurs since the value of identity column of the deleted row which is in Schema1.DIMCNAM
already exists in Schema2.DIMCNAM
October 14, 2009 at 1:06 pm
But errors occurs since the value of identity column of the deleted row which is in Schema1.DIMCNAM
already exists in Schema2.DIMCNAM
Right, that's what I was talking about. And what do you propose to do when this happens? Not insert it? Change the number? Replace the data? This is most likely a terrible idea and absolutely nothing you have said changes my mind on that.
At the very least, you should create a different identity column for the second schema's table, and use the identity value from the table1 as just an additional integer that is NOT an identity.
October 15, 2009 at 7:28 am
I have seen both the tables
Schema2.DIMCNAM has a Identity column col1
Schema1.DIMCNAM also has col1 as identity columns
We dont need to bother about Schema1.DIMCNAM
col1 has only 4 values on common in both the tables
so can i exclude those 4 common values and write the code as it is
the code will look like this
CREATE TRIGGER DIMCADR_TRG
ON
SCHEMA1.dbo.DIMCADR
FOR DELETE
AS
SET IDENTITY_INSERT SCHEMA2.dbo.DIMCADR ON
INSERT INTO Schema2.dbo.DIMCADR(co1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18, col19, col20, col21, col22, col23, col24, col25, col26, col27, col28, col29)
SELECT
Col1,
Col2,
'P',
Col4,
Col5,
Col6,
Col7,
Col8,
Col9,
Col10,
NULL,
NULL,
NULL,
NULL,
Col15,
Col16,
Col17,
Col18,
Col19,
Col20,
Col21,
Col22,
Col23,
Col24,
Col25,
Col26,
Col27,
Col28,
Col29
FROM DELETED
WHERE col NOT IN (9581, 9582, 14106, 14108)
SET IDENTITY_INSERT Schema2.dbo.DIMCADR OFF
So now i am excluding those 4 common values so that i will not get any error
Just check this and say whether my code is right and whether syntax for WHERE is right or not
But i have another problem with this code.
I ran a test on it.
Deleted data with those 4 values are not going to Schema2.DIMCNAM
but data regarding those 4 values is deleted in Schema1.DIMCNAM
I think the WHERE condition should be kept some where else
Can you suggest me?
October 15, 2009 at 12:32 pm
Deleted data with those 4 values are not going to Schema2.DIMCNAM
but data regarding those 4 values is deleted in Schema1.DIMCNAM
You still have not said what you want to do about this. You're well aware that you cannot insert these 4 values into Schema2 as they are. Excluding them from the insert makes the statement not fail(so far), but the deletes will still process from Schema1.
What would you LIKE to happen to these 4 rows(and any future ones whose identity is taken)? Do not just say 'insert them into schema2', because they cannot be inserted as is; the identity value is already used. How would you like to get around this?
October 15, 2009 at 12:55 pm
I am not inserting those 4 rows cause they are already in the Schema2.DIMCNAM
I am just leaving them as it is. I dont have to bother about future data since i am told to do so.
The fact is that ID columns will not have the same value in both tables.
But unfortunately 4 common values were detected. So i am just leaving them.
Is there any chance of keeping those 4 rows in Schema1.DIMCNAM even though they are deleted by someone. There is no need to do so but just to know.
I have another doubt ( may look stupid).
If someone opens the Schema1.DIMCNAM table and delete a value in random, then according to my code for trigger will the entire row of that specific deleted value will be deleted and goes to Schema2.DIMCNAM?
October 15, 2009 at 1:04 pm
rahulsony111 (10/15/2009)
I am not inserting those 4 rows cause they are already in the Schema2.DIMCNAMI am just leaving them as it is. I dont have to bother about future data since i am told to do so.
The fact is that ID columns will not have the same value in both tables.
But unfortunately 4 common values were detected. So i am just leaving them.
Is there any chance of keeping those 4 rows in Schema1.DIMCNAM even though they are deleted by someone. There is no need to do so but just to know.
OK, I relent. Here are your answers. I would absolutely not do this, but since you insist... You could add this at the top trigger to stop these rows from being deleted. While those 4 values are the only ones that match now, there's nothing to say that new ones won't match int he future, so checking the other table is safer. This code is not tested.
IF EXISTS(SELECT * FROM deleted WHERE col1 IN (SELECT col1 FROM Schema2.DIMCNAM))
BEGIN
RAISERROR('Row cannot be deleted because that ID value is being used by Schema2',16,1)
ROLLBACK TRAN
END
I have another doubt ( may look stupid).
If someone opens the Schema1.DIMCNAM table and delete a value in random, then according to my code for trigger will the entire row of that specific deleted value will be deleted and goes to Schema2.DIMCNAM?
"Deleting" a value in a column is not actually a delete, it's an update. Only deleting an entire row is a delete, so you're safe there based on trigger type.
October 15, 2009 at 1:14 pm
So if at all i want to write a code for a trigger for deletion of a value then should i write a trigger for UPDATE?
October 15, 2009 at 1:20 pm
October 15, 2009 at 1:24 pm
Thank you very much man.
Your help is really appreciable.
October 16, 2009 at 6:13 am
My task has been changed now a little bit but makes a big diference in code.
I need to setup a trigger so that whenever a certain column is removed from Schema1.DIMCNAM those column values should be added to Schema2.DIMCNAM and deletd from Schema1.DIMCNAM
So How can i modify the present code?
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply