DB21034E with triggers

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • =?ISO-8859-2?Q?Gregor_Kova=E8?=
    #1

    DB21034E with triggers

    Hi!

    My trigger looks like this:

    CREATE TRIGGER REVIZIJA.T_1_AU AFTER UPDATE ON TABLE1
    REFERENCING OLD O
    NEW N
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC

    DECLARE OLD_VALUE VARCHAR(120);
    DECLARE NEW_VALUE VARCHAR(120);

    SET OLD_VALUE = SUBSTR(O.VALUE, 1, 120);
    SET NEW_VALUE = SUBSTR(N.VALUE, 1, 120);

    INSERT INTO LOG.TABLE (ID, OLD, NEW) VALUES (NEXTVAL FOR TABLE_SEQ,
    OLD_VALUE, NEW_VALUE);
    END

    It is true that VALUE column in table TABLE1is defined as
    VARCHAR(30000). Is there any way to make this trigger compile?

    Best regards,
    Kovi

    --

    _______________ _____________
    |http://kovica.blogspot .com|
    -----------------------------~-~-~-~-~-~-~-~-~-~-
    | In A World Without Fences Who Needs Gates? |
    | Experience Linux. |
    -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
    Tags: None
    • Serge Rielau
      #2
      Re: DB21034E with triggers

      Gregor Kovaè wrote:
      Hi!
      >
      My trigger looks like this:
      >
      CREATE TRIGGER REVIZIJA.T_1_AU AFTER UPDATE ON TABLE1
      REFERENCING OLD O
      NEW N
      FOR EACH ROW MODE DB2SQL
      BEGIN ATOMIC
      >
      DECLARE OLD_VALUE VARCHAR(120);
      DECLARE NEW_VALUE VARCHAR(120);
      >
      SET OLD_VALUE = SUBSTR(O.VALUE, 1, 120);
      SET NEW_VALUE = SUBSTR(N.VALUE, 1, 120);
      >
      INSERT INTO LOG.TABLE (ID, OLD, NEW) VALUES (NEXTVAL FOR TABLE_SEQ,
      OLD_VALUE, NEW_VALUE);
      END
      >
      It is true that VALUE column in table TABLE1is defined as
      VARCHAR(30000). Is there any way to make this trigger compile?
      I think you may be stuck here. DB2 needs to be able to build a temp with
      both the old and new value.

      VARCHAR(32000) is a lot of text....

      --
      Serge Rielau
      DB2 Solutions Development
      IBM Toronto Lab

        Comment

        • =?ISO-8859-2?Q?Gregor_Kova=E8?=
          #3
          Re: DB21034E with triggers

          Hi!

          Yes, a lot of text. It is just that I'm building sort of an archiving
          system and on update I'd need new and old values.
          Can I solve this some other way?

          Best regards,
          Kovi

          Serge Rielau pravi:
          Gregor Kovaè wrote:
          >Hi!
          >>
          >My trigger looks like this:
          >>
          >CREATE TRIGGER REVIZIJA.T_1_AU AFTER UPDATE ON TABLE1
          >REFERENCING OLD O
          > NEW N
          >FOR EACH ROW MODE DB2SQL
          >BEGIN ATOMIC
          >>
          >DECLARE OLD_VALUE VARCHAR(120);
          >DECLARE NEW_VALUE VARCHAR(120);
          >>
          >SET OLD_VALUE = SUBSTR(O.VALUE, 1, 120);
          >SET NEW_VALUE = SUBSTR(N.VALUE, 1, 120);
          >>
          >INSERT INTO LOG.TABLE (ID, OLD, NEW) VALUES (NEXTVAL FOR TABLE_SEQ,
          >OLD_VALUE, NEW_VALUE);
          >END
          >>
          >It is true that VALUE column in table TABLE1is defined as
          >VARCHAR(30000) . Is there any way to make this trigger compile?
          I think you may be stuck here. DB2 needs to be able to build a temp
          with both the old and new value.
          >
          VARCHAR(32000) is a lot of text....
          >
          --
          _______________ _____________
          |http://kovica.blogspot .com|
          -----------------------------~-~-~-~-~-~-~-~-~-~-
          | In A World Without Fences Who Needs Gates? |
          | Experience Linux. |
          -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

            Comment

            • Serge Rielau
              #4
              Re: DB21034E with triggers

              Gregor Kovaè wrote:
              Hi!
              >
              Yes, a lot of text. It is just that I'm building sort of an archiving
              system and on update I'd need new and old values.
              Can I solve this some other way?
              Why do you need to store both before and after image?


              --
              Serge Rielau
              DB2 Solutions Development
              IBM Toronto Lab

                Comment

                • =?ISO-8859-2?Q?Gregor_Kova=E8?=
                  #5
                  Re: DB21034E with triggers

                  Unfortunately, yes.

                  Best regards,
                  Kovi

                  Serge Rielau pravi:
                  Gregor Kovaè wrote:
                  >Hi!
                  >>
                  >Yes, a lot of text. It is just that I'm building sort of an archiving
                  >system and on update I'd need new and old values.
                  >Can I solve this some other way?
                  Why do you need to store both before and after image?
                  >
                  >
                  --
                  _______________ _____________
                  |http://kovica.blogspot .com|
                  -----------------------------~-~-~-~-~-~-~-~-~-~-
                  | In A World Without Fences Who Needs Gates? |
                  | Experience Linux. |
                  -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

                    Comment

                    Previous template Next
                    Working...