You need to sign in to do that
Don't have an account?
GoodGroove
Opportunity.ID not found in SOQL query
Hi,
need help regariding my trigger below. Get this error code (SOQL does not contain results):
Severity and Description Path Resource Location Creation Time Id
System.DmlException: Update failed. First exception on row 0 with id 00620000005uHyQAAU; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, simpleTrigger02: execution of AfterUpdate caused by: System.QueryException: List has no rows for assignment to SObject Trigger.simpleTrigger02: line 77, column 61 SFDC_Ph01_deploy_Renewal_Trigger/src/unpackaged/classes simpleTrigger02testClass03.cls line 138 1220262381688 2940
Question: Red marked code is causing error (below: Oli1). Why does APEX does not find my Opportunity ID? The log tells me that oppty1.id and oppty2.id is existing!
CODE:
trigger simpleTrigger02 on Opportunity (after update) {
OpportunityLineItem oli1 = new OpportunityLineItem();
OpportunityLineItem oli2 = new OpportunityLineItem();
OpportunityLineItem oli3 = new OpportunityLineItem();
OpportunityLineItem oli4 = new OpportunityLineItem();
Opportunity oppty2 = new Opportunity();
Opportunity oppty3 = new Opportunity();
Opportunity oppty4 = new Opportunity();
List<OpportunityLineItem> oliList2 = new List<OpportunityLineItem>();
List<OpportunityLineItem> oliList3 = new List<OpportunityLineItem>();
List<OpportunityLineItem> oliList4 = new List<OpportunityLineItem>();
List<OpportunityLineItem> oliListX = new List<OpportunityLineItem>();
List<Opportunity> T_new = Trigger.new;
ID oid1 = T_new.get(0).id;
List<Opportunity> T_old = Trigger.old;
ID oid2 = T_old.get(0).id;
for (Opportunity oppty1 : Trigger.new){
if (oppty1.renew__c == true){
for (Opportunity opptytest1 : Trigger.old) {
Double cdate = oppty1.RenewalTermCalcDouble__c;
Integer i = cdate.intValue();
oppty2.accountid = oppty1.accountid;
oppty2.CustomerOrderDate__c = oppty1.CustomerOrderDate__c;
oppty2.CustomerOrderedBy_del__c = oppty1.CustomerOrderedBy_del__c;
oppty2.CustomerOrderID__c = oppty1.CustomerOrderID__c;
oppty2.CustomerOrderNumberAdditonal__c = oppty1.CustomerOrderNumberAdditonal__c;
oppty2.DateOfDelivery__c = oppty1.DateOfDelivery__c;
oppty2.Description = 'Angelegt am: ' + dit.addhours(2);
oppty2.GMDRetention__c = oppty1.GMDRetention__c;
oppty2.OpportunityInitial__c = oppty1.id;
oppty2.InvoiceDate__c = oppty1.InvoiceDate__c;
oppty2.name = 'Renewal 1.Jahr';
oppty2.OwnerId = oppty1.OwnerId;
oppty2.Pricebook2Id = oppty1.Pricebook2Id;
oppty2.Probability = oppty1.Probability;
oppty2.Product__c = oppty1.Product__c;
oppty2.ProductOther__c = oppty1.ProductOther__c;
oppty2.RecordTypeId = '012200000000ZErAAM';
oppty2.Type = 'Renewal';
oppty2.RenewalTerm__c = oppty1.RenewalTerm__c;
oppty2.RenewalType__c = oppty1.RenewalType__c;
oppty2.RequirementsDescription__c = oppty1.RequirementsDescription__c;
oppty2.StageName = 'Renewal Open';
oppty2.Service__c = oppty1.Service__c;
oppty2.CloseDate = oppty1.CloseDate.addMonths(12);
System.debug('DIE OPPTY1.ID: **********************' + oppty1.id);
oli1.opportunityId = oppty1.id;
oli1.quantity = 200.0;
oli1.unitprice = 20.0;
oli1.PricebookEntryId = '01u20000000fK10AAE';
oliListX.add(oli1);
/*
insert oliList2;
*/
oli1 = [Select quantity, unitprice, pricebookentryid, opportunityid from OpportunityLineItem where PricebookEntry.product2.ActivateRenewal__c = TRUE AND PriceBookEntryID <> '01u20000000fK11AAE' AND OpportunityID = :oppty1.id limit 1];
insert oppty2;
System.debug('OPPTY1.ID: **********************' + oppty1.id);
System.debug(' OPPTY2.ID: **********************' + oppty2.id);
oli2.opportunityId = oppty2.id;
oli2.quantity = oli1.quantity;
oli2.unitprice = oli1.unitprice;
oli2.pricebookentryid = oli1.pricebookentryid;
oliList2.add(oli2);
insert oliList2;
}
}
}
}
need help regariding my trigger below. Get this error code (SOQL does not contain results):
Severity and Description Path Resource Location Creation Time Id
System.DmlException: Update failed. First exception on row 0 with id 00620000005uHyQAAU; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, simpleTrigger02: execution of AfterUpdate caused by: System.QueryException: List has no rows for assignment to SObject Trigger.simpleTrigger02: line 77, column 61 SFDC_Ph01_deploy_Renewal_Trigger/src/unpackaged/classes simpleTrigger02testClass03.cls line 138 1220262381688 2940
Question: Red marked code is causing error (below: Oli1). Why does APEX does not find my Opportunity ID? The log tells me that oppty1.id and oppty2.id is existing!
CODE:
trigger simpleTrigger02 on Opportunity (after update) {
OpportunityLineItem oli1 = new OpportunityLineItem();
OpportunityLineItem oli2 = new OpportunityLineItem();
OpportunityLineItem oli3 = new OpportunityLineItem();
OpportunityLineItem oli4 = new OpportunityLineItem();
Opportunity oppty2 = new Opportunity();
Opportunity oppty3 = new Opportunity();
Opportunity oppty4 = new Opportunity();
List<OpportunityLineItem> oliList2 = new List<OpportunityLineItem>();
List<OpportunityLineItem> oliList3 = new List<OpportunityLineItem>();
List<OpportunityLineItem> oliList4 = new List<OpportunityLineItem>();
List<OpportunityLineItem> oliListX = new List<OpportunityLineItem>();
List<Opportunity> T_new = Trigger.new;
ID oid1 = T_new.get(0).id;
List<Opportunity> T_old = Trigger.old;
ID oid2 = T_old.get(0).id;
for (Opportunity oppty1 : Trigger.new){
if (oppty1.renew__c == true){
for (Opportunity opptytest1 : Trigger.old) {
Double cdate = oppty1.RenewalTermCalcDouble__c;
Integer i = cdate.intValue();
oppty2.accountid = oppty1.accountid;
oppty2.CustomerOrderDate__c = oppty1.CustomerOrderDate__c;
oppty2.CustomerOrderedBy_del__c = oppty1.CustomerOrderedBy_del__c;
oppty2.CustomerOrderID__c = oppty1.CustomerOrderID__c;
oppty2.CustomerOrderNumberAdditonal__c = oppty1.CustomerOrderNumberAdditonal__c;
oppty2.DateOfDelivery__c = oppty1.DateOfDelivery__c;
oppty2.Description = 'Angelegt am: ' + dit.addhours(2);
oppty2.GMDRetention__c = oppty1.GMDRetention__c;
oppty2.OpportunityInitial__c = oppty1.id;
oppty2.InvoiceDate__c = oppty1.InvoiceDate__c;
oppty2.name = 'Renewal 1.Jahr';
oppty2.OwnerId = oppty1.OwnerId;
oppty2.Pricebook2Id = oppty1.Pricebook2Id;
oppty2.Probability = oppty1.Probability;
oppty2.Product__c = oppty1.Product__c;
oppty2.ProductOther__c = oppty1.ProductOther__c;
oppty2.RecordTypeId = '012200000000ZErAAM';
oppty2.Type = 'Renewal';
oppty2.RenewalTerm__c = oppty1.RenewalTerm__c;
oppty2.RenewalType__c = oppty1.RenewalType__c;
oppty2.RequirementsDescription__c = oppty1.RequirementsDescription__c;
oppty2.StageName = 'Renewal Open';
oppty2.Service__c = oppty1.Service__c;
oppty2.CloseDate = oppty1.CloseDate.addMonths(12);
System.debug('DIE OPPTY1.ID: **********************' + oppty1.id);
oli1.opportunityId = oppty1.id;
oli1.quantity = 200.0;
oli1.unitprice = 20.0;
oli1.PricebookEntryId = '01u20000000fK10AAE';
oliListX.add(oli1);
/*
insert oliList2;
*/
oli1 = [Select quantity, unitprice, pricebookentryid, opportunityid from OpportunityLineItem where PricebookEntry.product2.ActivateRenewal__c = TRUE AND PriceBookEntryID <> '01u20000000fK11AAE' AND OpportunityID = :oppty1.id limit 1];
insert oppty2;
System.debug('OPPTY1.ID: **********************' + oppty1.id);
System.debug(' OPPTY2.ID: **********************' + oppty2.id);
oli2.opportunityId = oppty2.id;
oli2.quantity = oli1.quantity;
oli2.unitprice = oli1.unitprice;
oli2.pricebookentryid = oli1.pricebookentryid;
oliList2.add(oli2);
insert oliList2;
}
}
}
}
Have you tried a simplified version of the code? eg:
By the way, you might want to use the sObject.clone() function if you want to copy an object.
thank you very much for your comment.
The strange thing is that the System.Debug works:
20080902112816.064:Trigger.simpleTrigger02: line 33, column 9: oppty1.id = 00620000005uSDjAAM
This evidences the system knows "oppty1.id".
But for:
oli1 = [Select id, quantity, unitprice, pricebookentryid, opportunityid from OpportunityLineItem where OpportunityId = :oppty1.id] limit 1];
I only get empty rows back. Why?
Thank you for your help!
Regards, Ruben
You seem to be definind oli1, but not saving it.
I have tried to do so with the following result:
***********************************************************
Update oli1; -->
Severity and Description Path Resource Location Creation Time Id
System.DmlException: Update failed. First exception on row 0 with id 00620000005uVbXAAU; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY,
simpleTrigger02: execution of AfterUpdate
caused by: System.DmlException: Update failed. First exception on row 0; first error:
MISSING_ARGUMENT, Id not specified in an update call
Trigger.simpleTrigger02: line 78, column 13
SFDC_Ph01_deploy_Renewal_Trigger/src/unpackaged/classes simpleTrigger02testClass03.cls line 138 1220421503334 3534
***********************************************************
Insert oli1; -->
Severity and Description Path Resource Location Creation Time Id
System.DmlException: Update failed. First exception on row 0 with id 00620000005uVciAAE; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY,
simpleTrigger02: maximum trigger depth exceeded
Opportunity trigger event AfterUpdate for 00620000005uVci
Opportunity trigger event AfterUpdate
for 00620000005uVci
Opportunity trigger event AfterUpdate for 00620000005uVci
Opportunity trigger event AfterUpdate for 00620000005uVci
Opportunity trigger event AfterUpdate for 00620000005uVci
Opportunity trigger event AfterUpdate for 00620000005uVci
Opportunity trigger event
AfterUpdate for 00620000005uVci
Opportunity trigger event AfterUpdate for 00620000005uVci
Opportunity trigger event AfterUpdate for
00620000005uVci
Opportunity trigger event AfterUpdate for 00620000005uVci
Opportunity trigger event AfterUpdate for 00620000005uVci
Opportunity
trigger event AfterUpdate for 00620000005uVci
Opportunity trigger event AfterUpdate for 00620000005uVci
Opportunity trigger event AfterUpdate for
00620000005uVci
Opportunity trigger event AfterUpdate for 00620000005uVci
Opportunity trigger event AfterUpdate for 00620000005uVci
SFDC_Ph01_deploy_Renewal_Trigger/src/unpackaged/classes simpleTrigger02testClass03.cls line 138 1220421770560 3544
FAILURE MESSAGE: maximum trigger depth exceeded
Regards Ruben
That's complaining that you have a Trigger on opportunity and the Trigger is creating another opportunity, which is creating another Opportunity, etc.
In looking at your code, after an Opportunity is created you seem to be making a copy of the Opportunity, and saving it (hence the recursion).
So, from a high-level, what are you actually trying to achieve with all this code? That is, can you provide a description of your "requirements"?
again thank you for effort, it helps me a lot to share this problem.
What are doing with this code:
1) Somebody clicks a customButton "Renewal"
2) If the field renewal__c is set to true the trigger fires on update Opportunity
3) The trigger creates a new Opportunity, the Renewal according to the contract term (12, 24, 36)
4) If term is 24 it will create two one-year-term renewals, if 36 it will create three one-year-termed renewals
This works great.
The point is that I am not able to clone() the OpportunityLineItems from the original Opportunity "oppty1" which
is saved in the Trigger.
Doesn't the trigger store child object information like OpportunityLineItems from parent Opportunity?
The key point is: My SOQL for all OpportunityLineItems with Oppty1.id or Oppty2.id keeps empty.
Is this description helpful?
Thank you and best regards
Ruben
First, a point of logic: The current logic will copy the Opportunity ever time that it is updated as long as renew__c is true, even if somebody just updates a field (eg Stage). So, you should either reset renew__c at the end of the Trigger or insert some logic to only copy the Opportunity when it is first set to true (eg old.renew__c != new.renew__c).
Next, I was reading this post about cloning objects and managed to write the following code that clones an Opportunity and its line items. It ran successfully in my System Log ("Execute Apex"):
You might need to change the list of fields that are copied to meet your particular system and business rules.
That should be enough for you to adapt to your requirements, with a lot less lines of code.
Make sure you create test cases that check the logic around when the cloning should happen -- only once and only when renew__c is set to true.
Thank you so much for your contribution.
Here is the failure code in SalesForce after testing your code sample:1
TriggerOnOpportunityForRenewal01: execution of AfterUpdate caused by: System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, TriggerOnOpportunityForRenewal01: execution of AfterUpdate caused by: System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, TriggerOnOpportunityForRenewal01: execution of AfterUpdate caused by: System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, TriggerOnOpportunityForRenewal01: execution of AfterUpdate caused by: System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, TriggerOnOpportunityForRenewal01: execution of AfterUpdate caused by: System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, TriggerOnOpportunityForRenewal01: execution of AfterUpdate caused by: System.Exception: Too many DML rows: 195 Trigger.TriggerOnOpportunityForRenewal01: line 29, column 9 Trigger.TriggerOnOpportunityForRenewal01: line 29, column 9 Trigger.TriggerOnOpportunityForRenewal01: line 29, column 9 Trigger.TriggerOnOpportunityForRenewal01: line 29, column 9 Trigger.TriggerOnOpportunityForRenewal01: line 29, column 9: Trigger.TriggerOnOpportunityForRenewal01: line 29, column 9
It works untill the insert OpportunityLineItem Code.
Pls find the CODE below.
Regards
Ruben
trigger TriggerOnOpportunityForRenewal01 on Opportunity (after update) {
if (Trigger.new.get(0).description == 'HundKatzeMaus'){
System.debug('Trigger HundKatzeMaus auf Opportunity after update hat gefeuert!!!');
// Retrieve Opportunity
Opportunity o1 = [select Id, AccountId, name, Description, CloseDate, StageName, Amount, ExpectedRevenue, TotalOpportunityQuantity, Type, NextStep, ForecastCategory, Pricebook2Id, OwnerId from Opportunity where Id = :Trigger.new.get(0).ID];
// Clone Opportunity
Opportunity o2 = o1.clone(false, true);
o2.Name = o1.name + ' v2';
insert o2;
System.Debug('Created new opportunity: ' + o2.Id);
// Retrieve Opportunity line items
OpportunityLineItem[] lines = [select Id, OpportunityId, SortOrder, PricebookEntryId, Quantity, UnitPrice, ListPrice, ServiceDate, Description from OpportunityLineItem where OpportunityId = :Trigger.new.get(0).Id];
System.Debug('Found ' + lines.size() + ' lines');
// Clone them
List linesToAdd = new List();
for (OpportunityLineItem line : lines) {
OpportunityLineItem newLine = line.clone(false, true);
newLine.OpportunityId = Trigger.new.get(0).Id; // This links to the cloned Opportunity
linesToAdd.add(newLine);
}
insert linesToAdd;
Opportunity klonX = [select StageName,id,name from Opportunity where Id = :o1.id];
klonX.renew__c = false;
klonX.isRenewaled__c = false;
klonX.description = 'NotDogNotCatNotMouse';
update klonX;
}
}
How many rows are there on your Opportunity? This suggests that there's too many rows to process. You're allowed 100 per passed-in record. (See "Governor Limits" in the Apex Language Reference.)
Also, please note that the sample code I gave showed how to clone an Opportunity, but is not the ideal way to do so in a Trigger. Within the Trigger, you'll need to minimise the number of Select/Update/Insert calls:
- Use the passed-in Opportunities, rather than retrieving them again (as you do for o1 and klonX)
- Clone all Opportunities via a loop and then use one insert statement
- Retrieve all line items for all passed-in Opportunities in one select call
- Clone all line items via a loop (being careful to link them to the right cloned Opportunity) and then use one insert statement
- You don't have to reset renew__c -- you could check to see if the value changed instead
- All in all, you could use one select and two insert calls (or so it seems)
Yeah, it's complex stuff, isn't it!(By the way, if you paste code via the "SRC" button, it comes out looking nicer.)
Sorry for not answering these days, I have been testing around.
Still getting a similiar failure message:
Fehler:Apex-Auslöser TriggerOnOpportunityForRenewal01 hat eine unerwartete Ausnahme verursacht. Wenden Sie sich an Ihren Administrator: TriggerOnOpportunityForRenewal01: execution of AfterUpdate caused by: System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, TriggerOnOpportunityForRenewal01: execution of AfterUpdate caused by: System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, TriggerOnOpportunityForRenewal01: execution of AfterUpdate caused by: System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, TriggerOnOpportunityForRenewal01: execution of AfterUpdate caused by: System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, TriggerOnOpportunityForRenewal01: execution of AfterUpdate caused by: System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, TriggerOnOpportunityForRenewal01: execution of AfterUpdate caused by: System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, TriggerOnOpportunityForRenewal01: execution of AfterUpdate caused by: System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, TriggerOnOpportunityForRenewal01: execution of AfterUpdate caused by: System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, TriggerOnOpportunityForRenewal01: execution of AfterUpdate caused by: System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, TriggerOnOpportunityForRenewal01: execution of AfterUpdate caused by: System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, TriggerOnOpportunityForRenewal01: execution of AfterUpdate caused by: System.Exception: Too many SOQL queries: 21 Trigger.TriggerOnOpportunityForRenewal01: line 8, column 61 Trigger.TriggerOnOpportunityForRenewal01: line 31, column 8 Trigger.TriggerOnOpportunityForRenewal01: line 31, column 8 Trigger.TriggerOnOpportunityForRenewal01: line 31, column 8 Trigger.TriggerOnOpportunityForRenewal01: line 31, column 8 Trigger.TriggerOnOpportunityForRenewal01: line 31, column 8 Trigger.TriggerOnOpportunityForRenewal01: line 31, column 8 Trigger.TriggerOnOpportunityForRenewal01: line 31, column 8 Trigger.TriggerOnOpportunityForRenewal01: line 31, column 8 Trigger.TriggerOnOpportunityForRenewal01: line 31, column 8: Trigger.TriggerOnOpportunityForRenewal01: line 31, column 8
I have limited the rows, but...
Regards
Ruben
trigger TriggerOnOpportunityForRenewal01 on Opportunity (after update) {
if (Trigger.new.get(0).description == 'DogCatMouse'){
System.debug('Trigger DogCatMouse auf Opportunity after update hat gefeuert!!!');
// Retrieve Opportunity
Opportunity o1 = [select Id, AccountId, name, Description, CloseDate, StageName, Amount, ExpectedRevenue, TotalOpportunityQuantity, Type, NextStep, ForecastCategory, Pricebook2Id, OwnerId from Opportunity where Id = :Trigger.new.get(0).ID limit 1];
// Clone Opportunity
Opportunity o2 = o1.clone(false, true);
o2.Name = o1.name + ' v2';
insert o2;
//System.Debug('Created new opportunity: ' + o2.Id);
// Retrieve Opportunity line items
OpportunityLineItem[] lines = [select Id, OpportunityId, PricebookEntryId, Quantity, UnitPrice, ListPrice, ServiceDate, Description from OpportunityLineItem where OpportunityId = :Trigger.new.get(0).Id AND pricebookentryid //System.Debug('Found ' + lines.size() + ' lines');
// Clone them
List linesToAdd = new List();
for (OpportunityLineItem line : lines) {
OpportunityLineItem newLine = line.clone(false, true);
newLine.OpportunityId = Trigger.old.get(0).Id; // This links to the cloned Opportunity
linesToAdd.add(newLine);
}
insert linesToAdd;
Opportunity klonX = [select StageName,id,name from Opportunity where Id = :Trigger.old.get(0).id limit 1];
klonX.description = 'NotDogNotCatNotMouse';
update klonX;
}
}
This means you have hit the governor limit, which is 20 SOQL queries.
It seems to be caused by recursion. That is:
- Your trigger activates, and look for the Opportunity with description == 'DogCatMouse'
- The Opportunity is cloned and inserted
- The newly inserted Opportunity also has description == 'DogCatMouse', so it fires the trigger again
- This keeps happening until you hit the governor limit
In this specific case, you should change the description before you save the clone. That will prevent the recursion.I haven't tested this (I just typed it here), but you could try something like:
That should be bulk-friendly (will process any number of incoming Opportunities with only 1 select and 2 insert calls) and non-recursive (since it resets renew__c).
Dear John,
thank you so much for your contribution. I have managed it so far with this code:
But I will go over your code also, thanks a lot, without your help it would not have worked out.
Best regards
Ruben
- select Opportunity
- insert Opportunity
- select OpportunityLineItem
- select OpportunityLineItem
- select OpportunityLineItem
- select OpportunityLineItem
- insert Opportunity
- select Opportunity
- update Opportunity
Only 20 DML calls are permitted, so the Trigger will fail with more than 2 Opportunities being updated.The code sample I most recently provided had a minimal number of DML calls, yet can handle any number of Opportunities.
Hi,
I am New to salesForce. I had two issue.
1). using System.debug('Log messgae'); in trigger apex code, i can't the log in "System Log" link.
2). while using Trigger on Lead after update/insert i cant change the lead object value.
3). can you give any update example in apex or trigger(how to set the id).
example code:
trigger ChangeLeadCheckStatus on Lead (after insert, after update) {
system.debug('test Karthig');
if (true) {
Lead[] newLead = Trigger.new;
newLead[0].LastName = newLead[0].LastName + '_VB_';
}
}
1) Debug should work fine via the System Log link -- I cipied your line and it worked for me.
2+3) You can only change values in a BEFORE insert/update
If you have other questions, feel free to start a new discussion thread otherwise people probably won't find your questions.