Professional Web Applications Themes

Trigger + UpdLock - Microsoft SQL / MS SQL Server

Hi, First of all sorry for this looong question... I have two tables 1: CMENVI - with data changes 2: BAS_EMPRESA - data table On BAS_EMPRESA I have the following triggers on insert, update. CREATE TRIGGER PSNI_BAS_EMPRESA ON BAS_EMPRESA FOR INSERT, UPDATE AS DECLARE SEQUENCE NUMERIC(15,0) DECLARE CONTADOR NUMERIC(15,0) DECLARE TEXTO VARCHAR(8000) IF (SYSTEM_USER NOT LIKE 'REPL%') BEGIN SELECT SEQUENCE = MAX(iSEQ) FROM CMENVI WITH (updlock) IF (SEQUENCE IS NULL) SET SEQUENCE = 0 SET SEQUENCE = SEQUENCE + 1 SET CONTADOR = 0 INSERT INTO CMENVI ( dCDA, iSEQ, iNCO, sTBA, sCLA, sDEL, sICE, CT_char) SELECT CONVERT(DATETIME, GETDATE(), 103), ...

  1. #1

    Default Trigger + UpdLock

    Hi,

    First of all sorry for this looong question...

    I have two tables
    1: CMENVI - with data changes
    2: BAS_EMPRESA - data table

    On BAS_EMPRESA I have the following triggers on insert, update.

    CREATE TRIGGER PSNI_BAS_EMPRESA ON BAS_EMPRESA
    FOR INSERT, UPDATE
    AS
    DECLARE SEQUENCE NUMERIC(15,0)
    DECLARE CONTADOR NUMERIC(15,0)
    DECLARE TEXTO VARCHAR(8000)
    IF (SYSTEM_USER NOT LIKE 'REPL%') BEGIN
    SELECT SEQUENCE = MAX(iSEQ) FROM CMENVI WITH (updlock)
    IF (SEQUENCE IS NULL)
    SET SEQUENCE = 0
    SET SEQUENCE = SEQUENCE + 1
    SET CONTADOR = 0

    INSERT INTO CMENVI ( dCDA, iSEQ, iNCO, sTBA, sCLA, sDEL, sICE,
    CT_char) SELECT CONVERT(DATETIME, GETDATE(), 103), SEQUENCE,
    CONTADOR, 'BAS_EMPRESA', 'ESESTAB', 'N', 'S', ESESTAB from inserted
    SET CONTADOR = CONTADOR + 1

    IF UPDATE(ESNOME) BEGIN INSERT INTO CMENVI ( dCDA, iSEQ,
    iNCO, sTBA, sCLA, sDEL, sICE, CT_char) SELECT CONVERT(DATETIME,
    GETDATE(), 103), SEQUENCE, CONTADOR, 'BAS_EMPRESA', 'ESNOME', 'N',
    '', ESNOME from inserted SET CONTADOR = CONTADOR + 1 END

    IF UPDATE(ESRAZAO) BEGIN INSERT INTO CMENVI ( dCDA, iSEQ,
    iNCO, sTBA, sCLA, sDEL, sICE, CT_char) SELECT CONVERT(DATETIME,
    GETDATE(), 103), SEQUENCE, CONTADOR, 'BAS_EMPRESA', 'ESRAZAO', 'N',
    '', ESRAZAO from inserted SET CONTADOR = CONTADOR + 1 END

    UPDATE CMENVI SET INCO = CONTADOR WHERE ISEQ = SEQUENCE
    END


    After updated record, it should insert the changed data into table
    CMENVI, but only its primary key and the one changed. What this trigger
    do ..
    On column iseq goes the same number for all columns that have changed on
    the same record. If all 3 columns were changed (for example) I must have
    CMENVI like this if I update registries.

    Data Seq. Nr Cols. Table Column -- -- Value

    dCDA - iSEQ - iNCO - sTBA - sCLA - sDEL - sICE - CT_CHAR
    01/01/01 - 1 3 BAS_EMPRESA - 'ESESTAB' - 'N' - 'N' - '001'
    01/01/01 - 1 3 BAS_EMPRESA - 'ESNOME' - 'N' - 'N' - 'T1'
    01/01/01 - 1 3 BAS_EMPRESA - 'ESRAZAO' - 'N' - 'N' - 'TR1'

    01/01/01 - 2 3 BAS_EMPRESA - 'ESESTAB' - 'N' - 'N' - '002'
    01/01/01 - 2 3 BAS_EMPRESA - 'ESNOME' - 'N' - 'N' - 'T2'
    01/01/01 - 2 3 BAS_EMPRESA - 'ESRAZAO' - 'N' - 'N' - 'TR2'


    If I update registry by registry I have no problem, but if I do
    something like Update BAS_EMRPESA set ESNOME = ESNOME, to update all
    registries I get:
    Data Seq. Nr Cols. Table Column -- -- Value

    dCDA - iSEQ - iNCO - sTBA - sCLA - sDEL - sICE - CT_CHAR
    01/01/01 - 1 3 BAS_EMPRESA - 'ESESTAB' - 'N' - 'N' - '001'
    01/01/01 - 1 3 BAS_EMPRESA - 'ESNOME' - 'N' - 'N' - 'T1'
    01/01/01 - 1 3 BAS_EMPRESA - 'ESRAZAO' - 'N' - 'N' - 'TR1'

    01/01/01 - 1 3 BAS_EMPRESA - 'ESESTAB' - 'N' - 'N' - '002'
    01/01/01 - 1 3 BAS_EMPRESA - 'ESNOME' - 'N' - 'N' - 'T2'
    01/01/01 - 1 3 BAS_EMPRESA - 'ESRAZAO' - 'N' - 'N' - 'TR2'

    The two record with the same sequence.

    Is there any way to do this? I'm getting bold here.. :)

    Thanks in advance for any ligth!

    Ricardo Costa
    Florianópolis - SC - Brazil

    Ricardo Guest

  2. #2

    Default Re: Trigger + UpdLock

    I saw that when the trigger inserts tha values to other table, first it
    get all the first record, then the second and so on (If I make a
    complete update in the table (UPDATE TABLE SET COL = COL, for example)
    Is there a way to tell the trigger that it must first complete cicle and
    then go on to the next one?


    Thanks in advance

    ------------------------------
    Ricardo Costa
    IONICS Technology
    Florianopolis - SC - Brasil

    Ricardo Guest

  3. #3

    Default Re: Trigger + UpdLock

    The reason for your problem is that the trigger is only executed once even
    when you update multiple rows.

    When updating multiple rows, the INSERTED table will contain multiple rows.

    Your code finds the value of sequence in the beginning, and then uses the
    same value for all inserted rows.

    I think that you will have to create a cursor and loop through the inserted
    table record by record.

    /SG

    "Ricardo Costa" <com.br> wrote in message
    news:com.br...


    Stefan Guest

  4. #4

    Default Re: Trigger + UpdLock

    ok, but sequence is not inserted, how it is on inserted table? I
    mean, I get this variable to insert records on other table.. Even so
    this variable is on inserted table?
    (SELECT SEQUENCE = MAX(iSEQ) + 1 FROM CMENVI .....)
    (INSET INTO CMENVI....values(sequence, ...) )

    Thanks Stefan for the help

    Ricardo Costa



    Stefan Gustafsson wrote: 

    Ricardo Guest

Similar Threads

  1. XML.TRIGGER
    By Dan-C in forum Macromedia Flash Data Integration
    Replies: 1
    Last Post: February 28th, 03:55 PM
  2. Events don't trigger
    By ss yy via .NET 247 in forum ASP.NET Web Services
    Replies: 0
    Last Post: August 30th, 12:11 AM
  3. Trigger
    By James in forum ASP Database
    Replies: 1
    Last Post: August 6th, 01:05 AM
  4. Trigger Help
    By Mike in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 9th, 07:28 PM
  5. Trigger key
    By Elisabeth Smith in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 2nd, 09:22 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139