Solved

Exclude column of data type xml from update trigger

  • 19 November 2019
  • 6 replies
  • 211 views

Userlevel 2
Badge +2

I created a column in a table with the xml data type. However during execution of the source code of the update trigger of that table, I get the following error caused by this field.

Therefore I would like to exclude this field from the update trigger, but I cannot find a way to do this. I already set the “Trigger for update” attribute of the column to false in the subject, but that doesn’t do the trick.

The following highlighted code fragment in the update trigger is the problem I guess.

Is there a way to exclude this field from the automatically generated update trigger?

icon

Best answer by Jasper 20 November 2019, 10:59

View original

6 replies

Userlevel 7
Badge +11

Hi Dennis,

This code is generated by the `update_trigger_alleen_uitvoeren_na_wijziging` control procedure, which is of your company’s own business logic, not the Thinkwise Platform.

You should be able to exclude XML typed columns by modifying the parameter generation in the Code tab of the control procedure.

By the way, it shouldn’t be necessary to use `nolock` hints in your queries, since we use Read Committed Snapshot Isolation by default.

Userlevel 2
Badge +2

Dear Jasper,

Thanks  for your reply and for the additional tip about ‘no lock’ hints (relics from my predecessor) along the way!

I've found the code to add the check for domain/data type. I wonder if there is a column of c (see highlighted code) that I can use for checking the domain/ data type the same way that there is a check for ‘’calculated_field_type’?

I know that I can use something like this (see below), but I hope that there is a better way.:

SELECT DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'table name' AND
COLUMN_NAME = 'column name'

 

Userlevel 2
Badge +2

I've found a solution to exclude xml columns from the update triggers:

One question remains @Jasper:

What is the purpose of the ‘Trigger for update’ attribute in the subject (see below)? 

 

Userlevel 7
Badge +11

Hi Dennis,

Great that you have come up with a solution.

Remember to filter on the full key in the exists though (project_id, project_vrs_id, dom_id), or even better, use the dom table that is already available (line 79 in the screenshot above):

   and c.calculated_field_type = 0
   and d.dttp_id <> 'XML'

 

Userlevel 2
Badge +2

Hi Jasper,

That’s even better. Thanks!

 

What about:

What is the purpose of the ‘Trigger for update’ attribute in the subject? 

 

Or should I open a new post for that?

 

Userlevel 7
Badge +11

The Trigger for update option can be used to specify that a trigger should only be executed if (one of the) enabled columns are updated.

This is only implemented for DB2 for IBM i at the moment (using the `when` condition), not for SQL Server or Oracle.

If you need this feature on SQL Server you could add your own dynamic code template to triggers and use the `columns_updated` function (SQL Server 2016+) to check for modified columns. 

Reply