Google Cloud SQL Actions¶
Actions are the operations (or API operations) that you can perform on Google Cloud SQL irrespective of the database engine by simple drag-drop and data mapping of elements and values from the input trigger, derived values using formulas or desired constant that you may wish to map.
All of the actions that you see below automate what you can do manually on database like MySQL and PostgreSQL.
As shown below in Figure 1, you can Create, Update, Delete and Upsert (insert new or update the existing) records into a database table based on response to a trigger event.
Create Record¶
Create record(s) in your database on response to the event trigger into a selected table. This operation will be performed on the database engine you select.
To create this action you first need to select the table from the database in which you want to create the record. After the table selection, all the Not Null columns of your tables will be shown as required fields.
You can add optional fields to the table using the + button shown in Figure 2. You can map field data using the trigger data panel or you can use Excel-style functions. In the case of MySQL, you can also use the evaluate functionality to map values for enum type columns.
For example in Figure 2, values of multiple columns of MySQL are mapped to attributes from ServiceNow (Privileged). Similarly, you can also perform the operation on the database(s) with the PostgreSQL engine in Google Cloud SQL.
Delete Record¶
Delete one or multiple Google Cloud_SQL database records on response to an event trigger. You can filter records based on the selection criteria.
To create this action you first need to select the table in the database from which you want to delete the record(s).
Now you can add the conditions to select the records that will be deleted. You can construct the conditions using complex conditional OR (OR) and AND (+) logics.
For example, in Figure 3 below, from the MySQL table Incident, all the records which have incident name same as the category of ServiceNow incident and incident_state equal to closed will be deleted when an incident is updated in ServiceNow (Privileged). Similarly, you can also perform the operation on the database(s) with the PostgreSQL engine in Google Cloud SQL.
Update Record¶
Updates one or multiple Google Cloud SQL database records in response to an event trigger. You can update the record from the table based on some condition.
To create this action you first need to select the table in your database in which you want to update the records.
Now you can add the conditions to select the records that will be updated. You can construct the conditions using complex conditional OR (OR) and AND (+) logics.
You can select the updates that you want to perform on the selected records. You can use Data Mapping and Transformation and Excel-style functions. In the case of the MySQL database, you can also use the evaluate functionality to map values for enum type columns.
In Figure 4, whenever an Incident is updated on ServiceNow (Privileged), the record in the Incident table will be updated if both conditions below are satisfied.
- The value of the column Incident Description is equal to the value of Short Description from the ServiceNow trigger event.
- The value of the column Incident Name is equal to the value of Category from the ServiceNow trigger event.
The column incident_state is an enum data type column in the MySQL database. You can either select its value from the given options or you can map the value of this field using the evaluate functionality to the Incident State attribute from ServiceNow as shown in Figure 4.
Similarly, you can also perform the operation on the database(s) with the PostgreSQL engine in Google Cloud SQL.
Upsert Record¶
Upsert operation is the combination of Update and Create (insert) operations. Connect iPaaS will find if a record with matching Primary Key is present in the table. If a record is found, the record will be updated with the data provided. If a record is not found, Connect iPaaS will create a new record with the data provided. A primary key can either be a single column or a combination of columns.
In Figure 5 below, whenever an Incident is updated on ServiceNow (Privileged), a record is updated or added to the MySQL database. The Incident Name column in the database table Incident is mapped to attributes from ServiceNow.
Incident Description also mapped to attributes from ServiceNow and the excel function.
The column incident_state is an enum data type column in the MySQL database. You can either select its value from the given options or you can map the value of this field using the evaluate functionality by enabling the Evaluate checkbox.
Similarly, you can also perform the operation on the database(s) with the PostgreSQL engine in Google Cloud SQL.
