Results 1 to 7 of 7

Thread: Trigger

  1. #1
    Join Date
    Feb 2015
    Posts
    2

    Unanswered: Trigger

    Hello All,
    i have an accounting system running on sql,
    i would wish to update a column(Reconciled) on a table called postgl with the value '*'
    when we have the value 'true' on bReconciled please note that this
    is also on the same table postgl. Please help with this update trigger thanks in advace

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Is there anything else in the Reconciled column? If not, then you could just make it a computed column.

  3. #3
    Join Date
    Feb 2015
    Posts
    2
    Quote Originally Posted by MCrowley View Post
    Is there anything else in the Reconciled column? If not, then you could just make it a computed column.
    Sorry ,the reconcilled column should have an asterisk only when we have true on the breconcilled column.
    i have tried the following trigger and its not working

    CREATE TRIGGER trgUpdateCBg ON [dbo].[postgl]
    AFTER UPDATE
    AS
    DECLARE @status bit;
    DECLARE @state varchar(2)
    SELECT @status=breconciled,@state=reconciled
    FROM postgl;



    if @status='true' and (select postgl.reconciled from postgl where postgl.reconciled =@state)='null'

    UPDATE [dbo].[postgl]
    SET reconciled = '*'

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.

    And you need to read and download the PDF for:
    https://www.simple-talk.com/books/sq...l-code-smells/

    SQL is a declarative programming language. We do not like to use triggers because they are procedural code. We do not use BIT flags; that was assembly language programming. There is no generic “status” or “state” in RDBMS. These are attributes of something in particular -- “employment-status” for example.

    You are not writing SQL yet, but only using T-SQL to mimic a mag tape or punch card accounting package. Instead of punching a flag column in a card, which is what your trigger really is, an SQL programmer would compute a column or use a predicate in a VIEW. We do not use physical data like this.

    >> Sorry, the reconciled column should have an asterisk only when we have true on the reconciled column. I have tried the following trigger and its not working <<

    Why an asterisk? We do not do display formatting in a query in SQL.

    I also have the horrible feeling that you might also have debit and credit columns, along with other direct mimics of a classic set of paper books. But you did not post any DDL; read the sticky at the front of this group.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    So if there is no other data to be in this column, why not create a computed column? It will be ->FAR<- easier than a trigger, and much better for performance.
    Code:
    alter table test1 add computed_reconciled as case when breconciled = 'true' then '*' else null end

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Here's an NZDF script that might help:
    Code:
    CREATE TABLE doohickeys (
       thingwhoppie     INT         NOT NULL
    ,  whizzlefloop     DATETIME    NOT NULL
    ,  bReconciled      BIGINT      NULL
       )
    
    INSERT INTO doohickeys (thingwhoppie, whizzlefloop, bReconciled)
       VALUES ('42', '1753-04-01', 0), (69, CURRENT_TIMESTAMP, 1), (1e3, '9999-12-31', NULL)
    
    SELECT *
       FROM doohickeys
    
    ALTER TABLE doohickeys
       ADD postgl AS CASE WHEN 1 = bReconciled THEN '*' END
    
    SELECT *
       FROM doohickeys
    
    DROP TABLE doohickeys
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use Pat's computed column method or, if you must use a trigger, PLEASE do not write it as you have done above.
    All Triggers MUST be written to handle multi-record updates, and your trigger will only update a single record even if a thousand are affected by an update or insert statement.
    You can't load data into scalar variables and expect it not to come back and bite you at some time in the future.
    Instead, use the virtual INSERTED and DELETED tables, joining them on the primary key of your production table in order to update all the rows affected by an action.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •