Unable to delete from view as secondary table

[umasgowda]

Lets say I have 2 tables called consignments table(annotated with Table) and consignment_order_lines table. There is a view called consignments_list_view(annotated with Secondarytable) referenced from table consignments and consignment_order_lines. The requirement is to delete the record from consignments_list_view and the 2 tables. I can create a record in 2 tables and a view.
But I am unable to delete a record from view and 2 tables, while I try to delete through elide API, it returns SQL Exception
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: View or function 'consignments_list_view' is not updatable because the modification affects multiple base tables.

Is there any suggestions/solutions to fix this issue?

Example:

@include(name = "consignments", rootLevel = true)
@Getter
@Setter
@entity
@table(name = "consignments_list_view")
@SecondaryTable(name = "consignments")
@FieldNameConstants
@LifeCycleHookBinding(operation = CREATE, phase = PRECOMMIT, hook = ConsignmentsService.class)
@LifeCycleHookBinding(operation = CREATE, phase = PREFLUSH, hook = ConsignmentsService.class)
public class Consignments extends BaseModel implements Serializable {

private static final long serialVersionUID = 1L;

@Id
@Column(table = "consignments_list_view", name = "consignment_id", nullable = false)
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int consignment_id;

/**
 * The consignment reference is generated by the system so we don't expose the DB mapping,
 * the value is instead exposed using a computed attribute instead.
 */
@Exclude
@NotNull
@Column(table = "consignments", name = "consignment_ref", nullable = false, length = 20)
private String consignment_ref;

@ComputedAttribute
public String getConsignment_reference() { return this.consignment_ref; }

@NotNull
@Column(table = "consignments", name = "consignment_status", nullable = false, length = 20)
private ConsignmentsStatuses status;

@NotNull
@Column(table = "consignments", name = "dispatch_method", nullable = false, length = 20)
private DispatchMethodType dispatch_method;

@NotNull
@Column(table = "consignments", name = "fulfilment_type", nullable = false, length = 20)
private ConsignmentsFulfilmentType fulfilment_type;

@Exclude
@NotNull
@Column(table = "consignments", name = "deleted", nullable = false)
private Boolean is_deleted;

@Exclude
@NotNull
@Temporal(TemporalType.TIMESTAMP)
@Column(table = "consignments", name = "created_on", nullable = false)
private Date created_on;

@Exclude
@NotNull
@ManyToOne(optional = false)
@JoinColumn(table = "consignments", name = "created_by", nullable = false)
private Users created_by;

@OneToMany()
@JoinTable(
        name = "consignment_order_lines",
        joinColumns = @JoinColumn(name = "consignment_id"),
        inverseJoinColumns = @JoinColumn(name = "order_line_id")
)
private Collection<OrderLines> order_lines = new ArrayList<>();

@Temporal(TemporalType.TIMESTAMP)
@Column(table = "consignments_list_view", name = "despatch_sla_date", insertable = false, updatable = false)
private Date sla_delivery_date;

@OneToOne()
@JoinColumn(table = "consignments_list_view", name = "site_id", insertable = false, updatable = false)
private Sites site_ordered_for;

@OneToOne()
@JoinColumn(table = "consignments_list_view", name = "delivery_site_id", insertable = false, updatable = false)
private Sites site_delivered_to;

@OneToOne()
@JoinColumn(table = "consignments_list_view", name = "organisation_id", insertable = false, updatable = false)
private Organisations organisation_ordered_for;

@OneToOne()
@JoinColumn(table = "consignments_list_view", name="market_code", referencedColumnName = "market_code", insertable = false, updatable = false)
private Markets market;

public Consignments() {
    super();
}

@Override
public List<ValidationError> validate() throws ValidationException {
    return null;
}

}

[aklish]

For your view model, create a custom data store and transaction that inherits from the JPA store. This store will bind your view model whereas the default JPA store should be configured to bind all but this model. In your custom data store transaction, override delete or any other model mutations to delete from the underlying model/table. That will allow you to delete From a view.