Is manual Database.setSavepoint() and rollback needed on Trigger exception?

The reason to use Savepoints and Database rollbacks is when you are doing multiple operations in a single transaction, and want to "roll-back" the entire sequence of operations if there was an exception or problem later on in the process --- e.g., after prior DML operations had been successfully committed, or after a callout was processed, etc. Now, a Database.rollback CANNOT stop a callout from having been processed by an external server, but it CAN "undo" a whole bunch of DML operations all at once, which can be VERY complicated to do when you're doing Updates or Upserts --- without Rollbacks/Savepoints, you'd have to save all of your "initial" state records, as well as your modified versions, and then manually reconcile. Rollbacks/Savepoints save you from this hassle.

For instance, suppose that I want to programmatically create a new Account, Contact, and a Case record, all in a single transaction. BUT, if there is any error creating ANY of these, then I do not want ANY of these records to be saved. Without using Savepoints and Rollbacks, you would have to do a whole bunch of DELETES, checking along the way to ensure that everything saved successfully, since, if the Account record saves, it is SAVED, regardless of whether the Contact and Case save. With Savepoints and Rollbacks, though, you can ensure that NOTHING gets saved, if there's ANY errors, without doing a bunch of conditional checks and DELETES.

WITHOUT Savepoints

Account newAccount = new Account(Name='ACME Widgets');
Contact newContact = new Contact(FirstName='Murgatroid', LastName='Bullroarer');
Case newCase = new Case(
    Status = 'New',
    Priority = 'Normal',
    Subject = 'Broken Generator'
);

try {
   insert newAccount;
   // Now that our Account has saved,
   // set our COntact's AccountId
   newContact.AccountId = newAccount.Id;
   insert newContact;
   // Now that our Contact is saved,
   // set our Case's ContactId and AccountId
   newCase.AccountId = newAccount.Id;
   newCase.ContactId = newContact.Id;
   insert newCase;

} catch (Exception ex) {

  // Undo all of the inserts we may/may not have done
  // (a total waste of DML statements, considering the alternative with Savepoints)
  // We don't need to worry about the newCase having failed,
  // as it is the last in the sequence.
  if (newContact.Id != null) delete newContact;
  if (newAccount.Id != null) delete newAccount;

  // Add a page error / send an email / debug, something
  ApexPages.addMessages(ex);
}

WITH Savepoints

// Define the Database 'point' or 'state' to which we want to rollback
// if there are ANY errors throughout our transaction

Account newAccount = new Account(Name='ACME Widgets');
Contact newContact = new Contact(FirstName='Murgatroid', LastName='Bullroarer');
Case newCase = new Case(
    Status = 'New',
    Priority = 'Normal',
    Subject = 'Broken Generator'
);

Savepoint sp = Database.setSavepoint();

try {
   insert newAccount;
   // Now that our Account has saved,
   // set our COntact's AccountId
   newContact.AccountId = newAccount.Id;
   insert newContact;
   // Now that our Contact is saved,
   // set our Case's ContactId and AccountId
   newCase.AccountId = newAccount.Id;
   newCase.ContactId = newContact.Id;
   insert newCase;

} catch (Exception ex) {
  // Roll the database back to before we saved ANYTHING
  Database.rollback(sp);
  // Add a page error / send an email / debug, something
  ApexPages.addMessages(ex);
}

If an exception occurs, the transaction is automatically rolled back.

However if you specify an explicit false when using a Database method such as Database.update(acctList, false) - then the allOrNone behaviour is overridden, and a partial commit is allowed to happen.

The optional opt_allOrNone parameter specifies whether the operation allows partial success. If you specify false for this parameter and a record fails, the remainder of the DML operation can still succeed. This method returns a result object that can be used to verify which records succeeded, which failed, and why.

You generally use SavePoints when you want to ensure atomicity over a bunch of operations - eg you may have updated a record, and then made a callout to an external system - if the external system returns failure, and you want to roll back the transaction on salesforce, you would set a savePoint before the update and roll back to that.

The answers above appear to contradict each other, so it's worth adding some clarification. The need for Database.setSavepoint() and rollback depends in part on whether you are using a try/catch block in your code.

One way to prompt a SystemDmlException is to set a phone field to a value with more than 40 characters. If you use Execute Anonymous to run this code and then paste the Account ID from the debug log into your browser, you'll come up empty, because the whole transaction will have been rolled back automatically:

Account newAccount = new Account(Name='ACME Widgets');
Contact newContact = new Contact(FirstName='Murgatroid', LastName='Bullroarer');
Case newCase = new Case(
    Status = 'New',
    Priority = 'Normal',
    Subject = 'Broken Generator',
    SuppliedPhone = 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ'
);
insert newAccount;
System.debug(newAccount.Id);
newContact.AccountId = newAccount.Id;
insert newContact;
System.debug(newContact.Id);
newCase.AccountId = newAccount.Id;
newCase.ContactId = newContact.Id;
insert newCase;    

Make this slight change, and now you'll find that an Account record exists (although the Case record does not):

Account newAccount = new Account(Name='ACME Widgets');
Contact newContact = new Contact(FirstName='Murgatroid', LastName='Bullroarer');
Case newCase = new Case(
    Status = 'New',
    Priority = 'Normal',
    Subject = 'Broken Generator',
    SuppliedPhone = 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ'
);
try {
    insert newAccount;
    System.debug(newAccount.Id);
    newContact.AccountId = newAccount.Id;
    insert newContact;
    System.debug(newContact.Id);
    newCase.AccountId = newAccount.Id;
    newCase.ContactId = newContact.Id;
    insert newCase;    
} catch (Exception ex) {

}