| Author |
Topic |
|
iamsmahd
Yak Posting Veteran
55 Posts |
 Posted - 2005-09-22 : 07:41:43
|
| Hi....I have a trigger which uses a field in a table (t1) and calculates some other fields by a formula (written in the trigger) and runs by every insert & update action.I know that by inserting or updating my table the trigger will be run...I do not want to insert or update...just wanna to run my trigger in order to calculate the fields....Is there any special way to do it?Thank you very muchsonia |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-22 : 07:45:42
|
| I think Triggers will be fired only if there is transaction in the tableOtherwise just run an Update statement on that table to update the field that you want to update with the calculationMadhivananFailing to plan is Planning to fail |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-22 : 08:35:43
|
I expect you need something like this:CREATE TRIGGER MyTriggerON MyTableAFTER INSERT, UPDATEASUPDATE USET MyCalculatedColumn = I.MyCol1 * I.MyCol2FROM MyTable as U JOIN inserted AS I ON I.MyPKColumn = U.MyPKColumnWHERE MyCalculatedColumn IS NULL OR MyCalculatedColumn <> I.MyCol1 * I.MyCol2 The bit in blue is optional but worthwhile if an UPDATE is not always going to be required. (You might need to also handle where right hand side might become NULL and the original value is NOT NULL)Kristen |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-09-22 : 10:39:28
|
quote:
I know that by inserting or updating my table the trigger will be run...I do not want to insert or update...just wanna to run my trigger in order to calculate the fields....
Sounds like you don't want a trigger at all. What event will kick off this process if not an insert or update to the underlying table?Be One with the OptimizerTG |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-09-23 : 03:29:32
|
| I agree with TG, sounds to me as though a view or a stored procedure will do the jobsteveFacts are meaningless. You could use facts to prove anything that's even remotely true! |
|
|
|
fantasma
Starting Member
8 Posts |
Posted - 2005-09-23 : 05:47:02
|
| Having done a bit on triggers in the last couple of weeks I'd say that you don't want to use one for this. A trigger will fire every time the transaction (update, insert, or delete) that it is coupled with occurs. It is possible to use an instead of trigger to carry out a set of transactions/statements specified by yourself and prevent the insert/update/delete from occurring, if you wanted to. But I doubt you'd want to prevent all future inserts/updates/deletes on the table.I'd probably go with a stored procedure if you want to perform this task at your request. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-23 : 07:26:43
|
| Perhaps the question is actually "how to get the existing trigger to re-calculate the existing data retrospectively now that a calculation has been added to the trigger"If so the answer might be:UPDATE MyTableSET MyColumn = MyColumn -- Set it to itselfAdd a WHERE clause for "just" the broken records, if you can, to reduce the size of the update.Kristen |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-23 : 07:34:35
|
Kris, this is what I suggested him in my first reply  MadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-23 : 08:35:34
|
"Otherwise just run an Update statement on that table to update the field that you want to update with the calculation"That one?  I expect sonia read that, went away and tried it, solved the problem and we've heard nothing more. But we are all imagining what sort of problem we could help sonia solve!!Kristen |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-23 : 08:51:10
|
| ExactlyAre you a mind reader? MadhivananFailing to plan is Planning to fail |
|
|
|
|
trigger