[10.0.187 Pro] Triggers related issues

[10.0.187 Pro] Triggers related issues

Some issues listed bellow might be postgresql related

1. System triggers are not hidden
I found a few scripts which doesn't filter out system triggers. There are:
- Table Objects
- Objects (for both db versions).
A condition tgisingernal = FALSE is missing from those scripts

2. Only one event is shown for a trigger
If the trigger reacts on multiple types of events, in DB explorer only one event is shown (ie, only INSERT etc)

It comes from Table Triggers sql query. It's fragment looks like this:

	Code:
	SELECT tgname, 'TR', CASE WHEN tgtype & 4 <> 0 THEN 'INSERT'::TEXT WHEN tgtype & 8 <> 0 THEN 'DELETE'::TEXT WHEN tgtype & 16 <> 0 THEN 'UPDATE'::TEXT WHEN tgtype & 32 <> 0 THEN 'TRUNCATE'::TEXT END, '', 0 FROM pg_trigger WHERE tgrelid = :OBJECT_ID

To show all events, the code might look like the one below.

It's quick mockup but it works, maybe there is a way to write it smarter

Code:

SELECT tgname, 'TR',
array_to_string(array_remove(
ARRAY[]::VARCHAR[] ||
ARRAY[CASE WHEN tgtype & 4 <> 0 THEN 'INSERT'::VARCHAR ELSE NULL::VARCHAR END] ||
ARRAY[CASE WHEN tgtype & 8 <> 0 THEN 'DELETE'::VARCHAR ELSE NULL::VARCHAR END] ||
ARRAY[CASE WHEN tgtype & 16 <> 0 THEN 'UPDATE'::VARCHAR ELSE NULL::VARCHAR END] ||
ARRAY[CASE WHEN tgtype & 32 <> 0 THEN 'TRUNCATE'::VARCHAR ELSE NULL::VARCHAR END],
NULL), ',')::TEXT,
'',
0
FROM pg_trigger
WHERE tgrelid = :OBJECT_ID
AND NOT tgisinternal -- added to hide internal triggers

3. Edit DML Trigger opens new connection
There difference in behaviour between Edit DML trigger and Show DDL called over trigger (both run from context menu in DB Explorer).
Show DDL loads source code into current connection preview window. Edit DML trigger opens new connection, and then loads source into edit window. I think loading code into current connection preview window would be sufficient enough for both cases.

4. Show DDL run for trigger loads different objects code
While describing issue #3 I found that Show DDL feature, for some triggers shows trigger source code (CREATE TRIGGER...), for others it shows trigger function source code. In both cases code is loaded into preview window

With regards

A few more:
5. No trigger timing and mode shown
I modified a Table Triggers query to put all those information together with events.

BTW I tried to put trigger mode at the end of the name but obviously it didn't work for further trigger operations like Show code etc. Name is used as trigger identifier so cannot be changed. Having option to change displayed name would be useful in this case
6. No attributes and Extended properties for triggers
In case of triggers I failed in attempt to show data in columns Attributes and Extended properties. It would be useful, to have trigger event in one column, other information in another one. It would allow to sort triggers by event and then trigger name (postgresql executes triggers in alphabetical order)

My current version of code (a snippet from Table Triggers query)

Code:

SELECT tgname, 'TR',
CASE WHEN tgtype & 2 <> 0 THEN 'BEFORE: '::VARCHAR ELSE 'AFTER: '::VARCHAR END ||
array_to_string(array_remove(
ARRAY[CASE WHEN tgtype & 4 <> 0 THEN 'INS' ELSE NULL END] ||
ARRAY[CASE WHEN tgtype & 8 <> 0 THEN 'DEL' ELSE NULL END] ||
ARRAY[CASE WHEN tgtype & 16 <> 0 THEN 'UPD' ELSE NULL END] ||
ARRAY[CASE WHEN tgtype & 32 <> 0 THEN 'TRUNC' ELSE NULL END],
NULL), ',')::TEXT
   ||
' [' ||
      CASE tgenabled::TEXT
WHEN 'O' THEN 'ORIGIN'
WHEN 'D' THEN 'DISABLED'
WHEN 'R' THEN 'REPLICA'
WHEN 'A' THEN 'ALWAYS'
      END
|| ']',
'',
0
FROM pg_trigger
WHERE tgrelid = :OBJECT_ID
AND NOT tgisinternal

Many thanks. This is extremely valuable. All of the issues, except #3 can be certainly fixed in SQL Assistant options by tweaking the database catalog queries. And they should be added by default.
#3 is by design, each new editor is independent, and maintains its own connection, the editor from which it was opened can be safely closed or reconencted to a different database or server.

Last edited by SysOp on Wed Jan 30, 2019 11:53 pm; edited 1 time in total

Out of curiosity, have you tried right-click -> Properties menu for an individual trigger, or for the entire table? Do you see any issues in that area?

Thanx for the answer.
Regarding #3 I meant scenario which maybe is limited to postgresql only. Since in this database, working with trigger I might want to look at either trigger DDL or trigger function DDL, and since mentioned options shows one or another (considering it's not a bug), I thought it would be better to maintain behavioral consistency
Personally I like option to view code in preview pane first, then decide to move the code to query editor.

Question is, which part is wrong: the one when ;Show DDL' shows trigger function code, or second one, when it shows trigger DDL

And yes, I was using context menu over trigger (not properties). TBH I didn't know about this feature. Currently, for unknown reason I cannot see triggers in Properties window. I'll try defaults to check if it is not me who screwed it.

BTW IMO "Edit DML Trigger" is kinda misleading or invalid. Shouldn't it be "Edit Trigger DDL"? DML Trigger (which unfold to: Data Manipulation Language Trigger) doesn't make sense imo. But maybe I'm wrong ;)

I've no idea what you two are talking about just throwing my two cents regarding DML triggers.

In certain RDBMSs (eg. SQL Server) there are two kinds of triggers. The usual ones attached to tables are the ones that (usually) manipulate data, that is, they could be considered DML triggers. And there are those that fire in response to a some DDL events. Considering this, distinguishing the two using the expression "DML Trigger" does make sense.

Then again, I don't know, the same concept might not be present in PostgreSQL, so it might be confusing a bit, indeed.

Yes, you are right. It seems, this term comes from SQL Server (which I'm not familiar with)
I suspected that it might be about pinpoint the fact that this trigger is triggered by data change. On the other side that data change must not always be caused by DML (technically trigger reacts on change in data, not on language call)
But yeah, it's probably already settled term. Nothing can be done.
thanx

7. No triggers in Table Properties
I can confirm, even with default settings, Table properties shows no triggers for a table which has triggers associated.

May I ask which PG server version are you working with?

This might have been fixed already but I'm not 100% certain. Would it be possible to share DDL for the table and its triggers so that we can reproduce it locally? For the trigger functions we don't need the bodies, they can be blank if sharing that code is kind of sensitive. I think what is also important here for us is to reproduce the ownership of different objects and if they are in the same schema or different schemas.

I've created new database on my local machine, adding single table with single trigger. Still cannot see the trigger in table properties.
So it seems to be not schemata related (but who knows)

Thank. This has been confirmed as a bug. Actually it's a regression caused by changes in a seemingly unrelated function. Per internal bug notes, a fix is already developed and it's going to be available in 10.1 maintenance release.

Thank you for confirmation.

Please consider upgrading to version 10.1. The issue with triggers should be fixed now.