SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[10.0.187 Pro] Triggers related issues

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[10.0.187 Pro] Triggers related issues
Author Message
michalk



Joined: 29 Aug 2014
Posts: 211

Post [10.0.187 Pro] Triggers related issues Reply with quote
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
Mon Nov 12, 2018 10:15 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Reply with quote
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

Tue Nov 13, 2018 5:58 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
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
Tue Nov 13, 2018 9:59 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
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?
Tue Nov 13, 2018 10:15 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Reply with quote
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 ;)
Tue Nov 13, 2018 10:58 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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.
Tue Nov 13, 2018 11:08 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Reply with quote
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
Tue Nov 13, 2018 11:53 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Reply with quote
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.
Wed Nov 14, 2018 8:41 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
May I ask which PG server version are you working with?
Wed Nov 14, 2018 10:48 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Reply with quote
v9.6
Thu Nov 15, 2018 7:47 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
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.
Thu Nov 15, 2018 1:33 pm View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Reply with quote
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)
Mon Nov 19, 2018 8:00 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
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.
Mon Nov 19, 2018 10:06 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Reply with quote
Thank you for confirmation.
Mon Nov 19, 2018 10:14 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
Please consider upgrading to version 10.1. The issue with triggers should be fixed now.
Wed Jan 30, 2019 11:52 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.