October 16, 2009 at 8:06 am
That's not a little change.
What is the real goal here? It seems like auditing to me. You want to track historical values in your table. There are very valid reasons for doing this and much better ways of accomplishing it that don't result in this nightmare. Do you only want to track the previous value, or all previous values?
October 16, 2009 at 8:20 am
Even i dont know about my goal here exactly. I am very much confused.
I have to ask my boss again and make some clarifications.
In the technical Spec they gave like this
There are 2 triggers that need to be setup to remove certain columns form the GDW when a delete happens in OMS.
Triggered Table
Schema1.DIMCNAM
Where to be inserted
Schema2.DIMCNAM
We need a delete trigger to capture when a delete happens on the table DIMCNAM. When a delete happens we need to insert a record into Schema2.DIMCNAM
DIMCNAM
Original Value-----> Inserted Value
column1 --------> column1
column2------> column2
column3 -------> 'P'
column4 -------> column4
column5 -------> column5
column6 --------> if column6 = 'A' or 'B' then NULL ELSE FHAFEQ
column7 --------> if column6 = 'A' or 'B' then NULL ELSE FHAERS
column8 --------> if column6 = 'A' or 'B' then NULL ELSE FHAFES
column9 --------> if column6 = 'A' or 'B' then NULL ELSE FHAFET
column10 --------> if column6 = 'A' or 'B' then NULL ELSE FHAFES
column11 --------> if column6 = 'A' or "B' then NULL ELSE FHAFEU
column12 --------> column12
........................................
Column29-------->column29
This is the Specification given to me.
October 16, 2009 at 8:39 am
You didn't answer the question. What is the end goal? What are you using the schema2 table for? What purpose is it intended to serve? What is the business rule for doing this and the end goal of such a process.
Don't get me wrong, I'm not trying to be difficult. I'm just trying to find out if what you're asking to be done is going to result in what you actually want to do. It would likely work easier if you could explain to me what you want to actually happen, not keep asking specific questions for things that aren't going to work.
October 16, 2009 at 8:47 am
I edited my above reply
Please see the specification given to me.
I wrote exactly what it says in my specification.
I dont think u r difficult.
Actually problem is with me not with u.
Without knowing the goal how can even u say.
Anyways i am very much thankful to you cause u r listening to what i am saying with patience. I really appreciate it.
October 16, 2009 at 8:52 am
We need a delete trigger
This indicates to me that your original concept of a delete trigger was correct. You don't need an 'update' trigger which is what you'd need to have to track column level changes. You can make those transformations when the record is deleted.
October 16, 2009 at 8:56 am
But once in a meeting they said that they are going to delete certain columns like address line2, addressline 3.
Those values should be inserted in to Schema2.DIMCNAM.
That is what i am not clear about.
Anyways i have to make it clear myself but it takes time i think cause my boss will be back on Monday.
Thnak you
October 16, 2009 at 9:01 am
Are you saying that they are going to delete single values from those columns or they are literally deleting the entire column so that it won't exist in the table anymore?
Take this as you will, but in my opinion, this is just oversimplified auditing. They want a copy of the row inserted into schema2 every time an update or delete happens. I would create a new column on schema2 for identity, change column1(your current identity column) on schema2 to NOT be an identity and then do all of the transformations they are asking for in an update/delete trigger. You wouldn't need the SET IDENTITY_INSERT statements for this.
October 16, 2009 at 9:05 am
This may be auditing, but i dont have an idea bout auditing since i am new to sql.
They want to delete the entire column it seems.
What makes the difference if they want to delete entire column or value s in the column?
October 16, 2009 at 10:39 am
Deleting the entire column is a whole new ball of wax. That isn't data in the table changing, that is the table itself changing. To the best of my knowledge, you can't (in 2000) do anything in response to this with a trigger. In 2K5+ you can possibly use DDL triggers for this, but not in 2K.
October 16, 2009 at 11:13 am
So according to the spec i gave do you think that i have to write a code for deletion of columns?
I am using SQL SERVER 2005.
Can we write a DDL trigger for delete?
Does DDL triggers have Delete trigger?
The code which i wrote works only when a row is deleted, right?
How can i modify my code so that it should work when a value is deleted?
October 16, 2009 at 12:29 pm
Deletion of values is not the same thing as deletion of columns. This may help explain it, it may not. Don't run these, they're just to help explain.
You could do something like this:
Update yourtable
set col2 = NULL
where col1 = 12345
That is basically the 'deletion' of a 'value'. It deletes the value of col2 in a single row (or multiple rows). It is not truly a deletion at all, but an update to a new value of NULL.
The COLUMN deletion that I'm talking about is more like this:
ALTER TABLE yourtable DROP COLUMN col2
That would remove the column from the table and your table would NO LONGER HAVE a col2, period.
Personally, at this point, I would advise you to do some research and admit your confusion to whoever gave you the task in the first place. People don't like to admit that they don't understand things because they think it makes them look bad, but I'd personally much rather have someone admit that they didn't know how to do something than have them stab at it blindly and risk making a much larger mess in the process.
No offense intended, but if after this many posts you're still not even clear on what you need to do, you need to take a couple steps back from the problem and attempt to fully comprehend what the actual goal is and stop just trying to solve a problem you don't understand with code you don't know how to write.
October 16, 2009 at 12:38 pm
Ok man I got what you said.
So if we delete a value in a table will it by default set value as NULL.
I have to clarify somethings with my boss as u said.
But unfortunately i am running out of time. So i want to do this weekend. But still i am not sure of the goal.
I have to wait until monday.
Anyways thank you for the advice.
October 19, 2009 at 8:44 am
Hey i just confirmed with my boss and he said that it is right.
But do you remember about identity columns.i.e col1
We have 4 values in common in source and target tables
My boss told me to check if col1 value already exist in target table.
If exist then i have to assign col1 a higher value.i.e MAX(col1)+1
how can i modify the code for that?
October 19, 2009 at 10:49 am
And at that point, you've just lost even the 'perceived' benefit of manually inserting the identity column, because it won't link. Identity columns exist so that you don't have to do this type of manipulation. Stop trying to forcefeed the server integer values and let the identity column do its job. Use this:
CREATE TRIGGER DIMCADR_TRG ON SCHEMA1.dbo.DIMCADR
FOR DELETE
AS
INSERT INTO Schema2.dbo.DIMCADR (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 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
Note that I'm not forcing Col1 into schema2. Col1 will populate itself. That's what an identity column does. This will do everything you want it to do. This comes from one of your earlier posts, you may need to add in your case logic and that's fine, but stop trying to force identity values. It is giving you no value and will cause issues.
October 19, 2009 at 11:00 am
Ok i got it
so identity values will be taken by default, then that is fine.
Thank You
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply