| Author |
Topic |
|
pravin14u
Posting Yak Master
246 Posts |
 Posted - 2008-02-13 : 07:17:32
|
| Hi,Is there anyway where I can create a trigger which will execute before the update/delete takes place( and then the actual update/delete takes place)?Thanks,Prakash.P |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-13 : 07:23:15
|
| if you want trigger action before insert/update use INSTEAD OF trigger for update/insert & if you want action after use AFTER trigger for I/U. You cant have same trigger for both scenario. If you want actions to perform before & after insert update, you might need two triggers INSTEAD OF & AFTER. Before that can i ask why you need both? |
 |
|
|
pravin14u
Posting Yak Master
246 Posts |
Posted - 2008-02-13 : 07:42:07
|
| I am just trying to find out a work around for the following issue:"Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables."Can anyone help me please? Basically I have table A with 'text' column and I need to insert the modified(updated/deleted) values into another table B (which has exactly the same structure as A) |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-02-13 : 08:29:03
|
| If you JOIN inserted with <YourTable> by it's primary key, you can insert the <yourTable>.<TextCol> into your other tableB. (rather than inserted.<textCol>)Be One with the OptimizerTG |
|
|
|
pravin14u
Posting Yak Master
246 Posts |
Posted - 2008-02-13 : 08:33:46
|
| Exactly , I had this option but again, I cant be sure that my text column will not be updated and again I am in a soup:) |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-02-13 : 08:41:56
|
| >>I cant be sure that my text column will not be updated...need to insert the modified(updated/deleted) values into another table BWhy not? On a trigger for AFTER Insert,Update the actual table will hold the inserted/modified version - definately.Be One with the OptimizerTG |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-13 : 11:05:47
|
| You can use UPDATE() function in AFTER insert trigger to check whether a column was updated |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-02-13 : 11:18:10
|
quote:
Originally posted by visakh16
You can use UPDATE() function in AFTER insert trigger to check whether a column was updated
If your style of update is to reference every column whether it changed or not then UPDATE(<col>) will always return true even if the value didn't change, so be careful there.Be One with the OptimizerTG |
|
|
|
pravin14u
Posting Yak Master
246 Posts |
Posted - 2008-02-13 : 23:50:00
|
| Sorry, I think I was not clear with my requirement.Say there was "SQL 2008 is good" in my text column(Table A) and now i have updated it to "SQL 2008 is great". My requirement is that I need "SQL 2008 is good" to be in my Table B. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-13 : 23:58:59
|
| If you have an AFTER UPDATE trigger, you can get both old and new values in it. on updating the above field value, if PK is primary key of record updated, SELECT textCol FROM DELETED WHERE PKcol=PK will give you old valuei.e,SQL 2008 is goodwhileSELECT textCol FROM INSERTED WHERE PKcol=PK will give you new valuei.e,SQL 2008 is great |
|
|
|
pravin14u
Posting Yak Master
246 Posts |
Posted - 2008-02-14 : 00:36:39
|
| Back to square one, the issue is that YOU CAN SELECT a TEXT, NTEXT or IMAGE column from DELETED table. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-02-14 : 08:25:16
|
| Can you perform your inserts and updates via Stored Procedure? If so, don't even use a trigger, put this logic in the SP.Otherwise, I see your problem: An INSTEAD of trigger would allow you to populate tableB but not put the "new" value in TableA, and an AFTER trigger will not allow you to populate tableB with the old value...Another reality check is to make sure your column needs to be TEXT. Are you using 2005? How big are the largest values going to be?Be One with the OptimizerTG |
|
|
|
pravin14u
Posting Yak Master
246 Posts |
Posted - 2008-02-18 : 03:54:42
|
| Hi TG,Can you please explain me how this can be done using a stored procedure?Thanks,Prakash.P |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-02-18 : 18:42:53
|
quote:
Originally posted by pravin14u
Hi TG,Can you please explain me how this can be done using a stored procedure?Thanks,Prakash.P
Well let me ask a question first. Do users access this table directly through sql client tools (like query analyzer) or through an application of some sort?Assuming it is an application and you don't need to worry about users bypassing the application and accessing the table directly, you can have the application call a stored procedure instead of an "inline" INSERT or UPDATE. The procedure would have a typed input parameter for each column in the table (including your TEXT column). You could then perform your INSERT/UPDATE to the target table. You could then also INSERT to your "tableB" based on your logic.I'm still curious if that column needs to be text. What are the answers to my "reality check" in my last post regarding the TEXT column?Be One with the OptimizerTG |
|
|
|
pravin14u
Posting Yak Master
246 Posts |
Posted - 2008-02-19 : 00:29:54
|
quote:
Originally posted by TG
quote:
Originally posted by pravin14u
Hi TG,Can you please explain me how this can be done using a stored procedure?Thanks,Prakash.P
I'm still curious if that column needs to be text. What are the answers to my "reality check" in my last post regarding the TEXT column?Be One with the OptimizerTG
This is an existing application and we are doing some modifications to it. We dont have access to either the application that users use to enter and update data or to change the relation structure. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-02-19 : 07:21:20
|
quote:
Originally posted by pravin14uThis is an existing application and we are doing some modifications to it. We dont have access to either the application that users use to enter and update data or to change the relation structure.
These two statements conflict with each other  But be that as it may...Then perhaps you can submit your requirements to the group that does maintain the application.Be One with the OptimizerTG |
|
|
|
Dilipv
Starting Member
4 Posts |
|
|
|
Before Trigger