| Author |
Topic |
|
notmyrealname
98 Posts |
 Posted - 2010-10-25 : 11:50:09
|
| Hi,I have a question regarding Instead Of triggers.I want to create a trigger that replaces one value of what the user is trying to insert. For instance, the user is adding a new row to a table that has 50 columns. I want to make the value in Col10 equal a value determined by a query againt the data that already resides in the table. I have no problem calculating the value i want assigned to Col10.My question is:Is there a way to just overwrite Col10 before it is added to the table? Should i use Instead Of Insert, After Insert, etc...?If i use Instead Of Insert i could completely control the data that goes into the table and this is most likely what i want to do. The only thing i don't want to do is explicitly define all 50 columns again just to update the Col10 value.I know i can do this:INSERT INTO MyTable SELECT * FROM InsertedThis would do exactly what the Insert would do anyway. Is there any way to just update the Col10 value only?Unforetunately i cannot do this:UPDATE Inserted SET Col10 = MyValueDoes anyone know a way to just update Col10 before the data is added to the table?Is there a way to declare a table variable that is already structured to match MyTable? Then i could just update the Col10 in my table variable and insert the results into the actual datatable.Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
notmyrealname
98 Posts |
Posted - 2010-10-25 : 12:45:54
|
| Basically, the user might add a record today and make a change to the Col10 value. Tomorrow he will add the same row again. When he inserts the new row i want to update the Col10 value to match the value he set the other row to. If there is no existing match then the new row is just inserted "as is". I don't think i can achieve what i am looking fo with computed columns. I might be wrong.Thanks. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
notmyrealname
98 Posts |
Posted - 2010-10-25 : 13:00:15
|
| Unforetunately i don't have Management Studio. Just Visual Studio 2005. I don't think i have options to automate the process. But at least i now know what i have to do.One more question, please.If MyTable is the table that i am applying the trigger to - do you know if there is a way to create a Table variable that can inherit MyTable's structure or would i also have to explicity create all of the table columns? I am just trying to avoid having to update several objects when i add new columns to this table later.Thanks for your help. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
notmyrealname
98 Posts |
Posted - 2010-10-25 : 13:24:27
|
| Wouldn't i be able to...1. DECLARE @MyTable AS Table equal to MyTable's structure2. Set the Col10 value for @MyTable3. INSERT INTO MyTable SELECT * FROM @MyTableIs there something object or variable that i can modify and then provide at the end of my INSERT command?Sorry for the sophomoric questions? I haven't really worked much with triggers and Table variables. I'll stop asking after this, i promise!Thanks again. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
notmyrealname
98 Posts |
Posted - 2010-10-25 : 13:45:19
|
quote:
Originally posted by tkizer
Could you get SQL Server Management Studio Express installed to help you with this? It's a free download and will greatly help you out with SQL Server development.
Could you please call my IT guy? I've been asking for years!Thanks for all the advice. It's not the end of the world to explicitly define the columns in my update. I'll give it a try.Thanks again. |
|
|
|
notmyrealname
98 Posts |
Posted - 2010-10-25 : 20:11:21
|
After a little more research it actually turned out to be pretty simple. Unless i am missing something.ALTER TRIGGER InterceptNewValue ON ActualTable INSTEAD OF INSERTAS -- Create a temp copy of the Inserted table SELECT * INTO #MyTable FROM Inserted -- Update my temp table as needed. -- The logical table Inserted cannot be updated -- with an UPDATE query. UPDATE #MyTable SET [AnyColumn] = (SELECT ... FROM MyTable ...) -- Insert data from temp table instead of the -- data in the Inserted table. INSERT INTO ActualTable SELECT * FROM #MyTable Now i don't have to worry about updating this trigger if i make changes to the ActualTable structure (i.e. add, remove, rename columns). I just have to make sure that the [AnyColumn] column does not change.Still deciding if i should do this with the trigger or set the value in my application (ADO.Net).Anyway, thought i'd keep you posted.Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-26 : 02:21:46
|
Why not use an AFTER trigger?ALTER TRIGGER InterceptNewValue ON ActualTable AFTER INSERTAS UPDATE U SET [AnyColumn] = (SELECT ... FROM MyTable ...) FROM ActualTable AS U JOIN inserted AS I ON I.MyID = U.MyID |
|
|
|
notmyrealname
98 Posts |
Posted - 2010-10-26 : 08:48:33
|
quote:
Originally posted by Kristen
Why not use an AFTER trigger?
It's kind of complicated. How do i explain?Let's say the following info is already in the table...Part SomeValue------ ----------100 123101 123101 123101 123 You'll notice that the same part can be entered many times. You can also see that the SomeValue will always be the same for each unique part (i.e. Part 101 has SomeValue = 123). I have an UPDATE trigger in place to ensure that the SomeValues always remain the same.Now...Let's say i have a new value being added to the table.Part SomeValue------ ----------101 NULL I want to first look and see if the part already exists in the table. If it does, then i want the newly added SomeValue to inherit the existing SomeValue for that part. If it doesn't already exist then part can be added as is. Because i know that the SomeValues are always the same for each part, i can just select the first matching part and copy its SomeValue to the new part.The problem with the AFTER trigger is that my data would now look like this...Part SomeValue------ ----------100 123101 123101 123101 123101 NULL Now i have no way of determining which SomeValue was the original unless i add some timestamp which i could do but i just don't want to.Let me point out that i am still pretty new to these differnt variations of triggers. That is how i think the AFTER trigger works. Please correct me if i am wrong. Is there a way using an AFTER trigger to capture the existing data before the trigger fires and then perform the update after?Thanks for the suggestion. I really appreciate the help. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-26 : 08:56:07
|
| So you have no Primary Key on the table, and no combination of columns that are unique?(If so its not a good table design! I'm afraid) |
|
|
|
notmyrealname
98 Posts |
Posted - 2010-10-26 : 09:06:09
|
| Sorry. The table was just an example to illustrate the columns that needed to be analyzed. There are in fact unique part id's. The primary key is actually [Project, PartID]. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-26 : 09:11:10
|
In that case:ALTER TRIGGER InterceptNewValue ON ActualTable AFTER INSERTAS UPDATE U SET [AnyColumn] = (SELECT ... FROM MyTable ...) FROM ActualTable AS U JOIN inserted AS I ON I.Project = U.Project AND I.PartID = U.PartID |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-26 : 09:13:37
|
| Note that the value will be "wrong" between the actual INSERT and until the Trigger has finished. BUT ... that is within a transaction, so nothing else will be able to see the intermediate value (unless some twit uses the NOLOCK hint or somesuch) ... but it would create a problem if, for example, you had a CHECK CONSTRAINT on the column that was violated - e.g. if you are saving this value with a NULL in this column, relying on the Trigger to "fix up" the actual value, but the underlying table does not allow NULLs |
|
|
|
notmyrealname
98 Posts |
Posted - 2010-10-26 : 09:20:34
|
| So is the AFTER INSERT an additional event that happens after the default INSERT whereas the INSTEAD OF INSERT completely replaces the default INSERT?So in your trigger, does the data in the ActualTable have (at the time the trigger fires) the new value in it? Wouldn't this still prevent me from determining what the existing value is without adding some sort of timestamp column?Thanks. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-26 : 09:36:27
|
| 1. Yes2. When the trigger files [ActualTable] contains the new data (not-yet-modified by your trigger), and [INSERTED] pseudo-table has the same data (but only for row(s) which are part of the Insert [and in a DELETE or UPDATE the [DELETED] pseudo-table has the original data, before the Delete/Update happened] |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-26 : 09:38:16
|
| Note that an AFTER trigger doesn't need special logic to make sure it can always handle all modifications to the table, so I think it is preferable in this scenario (unless someone tells me that the fact that an AFTER triggers causes two-updates to happen is Bad News!) |
|
|
|
notmyrealname
98 Posts |
Posted - 2010-10-26 : 09:58:32
|
| Alright. I'll see if i can implement it. Thanks. |
|
|
|
Next Page
|
Instead Of Insert Trigger