db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4988) ALTER TABLE DROP COLUMN should make use of information in SYSTRIGGERS to detect column used through REFERENCING clause to find trigger dependencies
Date Thu, 03 Feb 2011 03:23:28 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4988?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12989951#comment-12989951
] 

Dag H. Wanvik commented on DERBY-4988:
--------------------------------------

Some comment to the patch:

- AlterTableConstantAction

* // otherwsie there would be unexpected behaviors

  Apart from the typo, is this not SQL requirement?

* The "change" variable can probably be eliminated, since this test is
  repeated over again at end of loop:

         referencedCols[j] > droppedColumnPosition

  i.e. the first test inside the loop to set "change" could be
  omitted, too. Test test on "j == refColLen" is a bit opaque, as far
  as I can tell it means here that we did see the trigger does not
  depend on the column being dropped, but may need adjustment of one
  or more of its columns. May be worth a clarification. Could the
  column index adjustment be moved outside the loop, perhaps (we are
  logically done when j == refColLen).

* changedColPositionInTriggerAction is set but not used anywhere?
  Shouldn't the positions in referencedColsInTriggerAction be adjusted
  above droppedColumnPosition, too?

- AlterTableTest

* In the last test case added:
  //Now try ALTER TABLE DROP COLUMN CASCADE where the column being
  //dropped is in trigger action of trigger defined on a different table
 
  the test then goes on to say:
  // following is not the right behavior. we should have gotten an error
  // because column being dropped is getting used in a trigger action 

  I thought that the issue here would be that the trigger isn't
  dropped? (since we have cascade mode)


> ALTER TABLE DROP COLUMN should make use of information in SYSTRIGGERS to detect column
used through REFERENCING clause to find trigger dependencies
> ---------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-4988
>                 URL: https://issues.apache.org/jira/browse/DERBY-4988
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.7.1.1
>            Reporter: Mamta A. Satoor
>            Assignee: Mamta A. Satoor
>             Fix For: 10.8.0.0
>
>
> At the time of ALTER TABLE DROP COLUMN, Derby checks if the column being dropped in a
trigger column and if so, then it will not drop the column if it is being done in RESTRICT
mode or it will drop the trigger while dropping the column in CASCADE mode. This does not
implement SQL standard to it's entirety. 
> **************************************
> SQL standard says following about ALTER TABLE DROP COLUMN RESTRICT and trigger dependency
in CREATE TRIGGER section 
> If RESTRICT is specified, then C shall not be referenced in any of the following 
> d) Either an explicit trigger column list or a triggered action column set of any trigger
descriptor. 
> (The triggered action column set included in the trigger descriptor is the set of all
distinct, fully qualified names of columns contained in the <triggered action>.) 
> **************************************
> What is missing from Derby implementation from SQL standard point of view is detected
triggered action column set.
> Starting 10.7(with DERBY-1482), Derby started keeping track of trigger action columns
which are referenced through REFERENCING clause. This information can be used to improve the
behavior of ALTER TABLE DROP COLUMN in 10.7 and higher. This will not cover all the trigger
action columns since columns referenced without the REFERENCING clause are not tracked anywhere
at this point. More work will need to be done to implement SQL standard completely. But we
can take a step forward by using the information available in 10.7 and higher to detect trigger
action columns which are referenced through REFERENCING clause

-- 
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message