Professional Web Applications Themes

trigger question - Microsoft SQL / MS SQL Server

Hi I have the following tables TranDetails (3rd Party) with 4 fields making up primary key Supplier char 7 GRN char 7 Journal decimal JournalEntry decimal detail1 detail1 detail3 InvMove Journal (PK) decimal JournalEntry (PK) decimal Stockcode char Supplier char Date date GRNDetails GRN Supplier Stockcode etc when a record is inserted into the TranDetails table I would like to insert a record into GRNDetails table based on a query from the InvMove and TransDetails table. I want the query to be limited to the record that has just been entered into the TranDetails table i.e. the query would only ...

  1. #1

    Default trigger question

    Hi

    I have the following tables TranDetails (3rd Party) with 4 fields making up
    primary key
    Supplier char 7
    GRN char 7
    Journal decimal
    JournalEntry decimal
    detail1
    detail1
    detail3

    InvMove
    Journal (PK) decimal
    JournalEntry (PK) decimal
    Stockcode char
    Supplier char
    Date date


    GRNDetails
    GRN
    Supplier
    Stockcode
    etc


    when a record is inserted into the TranDetails table I would like to insert
    a record into GRNDetails table based on a query from the InvMove and
    TransDetails table.
    I want the query to be limited to the record that has just been entered into
    the TranDetails table i.e. the query would only ever return one record

    How would I do this using a trigger? I am a complete beginner where this is
    concerned so any help would be greatly appreciated


    Newbie Guest

  2. #2

    Default Re: trigger question

    Make use of INSERTED temporary table,the inserted table stores copies of the
    affected rows during INSERT. this table is visible to trigger body only. ie
    if you are writing a trigger FOR INSERT operation, you can refer to this
    table inside the trigger body.
    See BOL for more information.

    --
    -Vishal
    "Newbie" <noideanospam.com> wrote in message
    news:eJL1Se9PDHA.3016TK2MSFTNGP10.phx.gbl...
    > Hi
    >
    > I have the following tables TranDetails (3rd Party) with 4 fields making
    up
    > primary key
    > Supplier char 7
    > GRN char 7
    > Journal decimal
    > JournalEntry decimal
    > detail1
    > detail1
    > detail3
    >
    > InvMove
    > Journal (PK) decimal
    > JournalEntry (PK) decimal
    > Stockcode char
    > Supplier char
    > Date date
    >
    >
    > GRNDetails
    > GRN
    > Supplier
    > Stockcode
    > etc
    >
    >
    > when a record is inserted into the TranDetails table I would like to
    insert
    > a record into GRNDetails table based on a query from the InvMove and
    > TransDetails table.
    > I want the query to be limited to the record that has just been entered
    into
    > the TranDetails table i.e. the query would only ever return one record
    >
    > How would I do this using a trigger? I am a complete beginner where this
    is
    > concerned so any help would be greatly appreciated
    >
    >

    Vishal Parkar Guest

  3. #3

    Default Re: trigger question

    Thanks for that but I am not sure understand what you mean . . . ..

    Would I need to do something like:

    CREATE TRIGGER newrecord
    ON TranDetails
    FOR INSERT
    AS
    SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    dbo.TranDetails.OrigReceiptDate
    FROM dbo.TranDetailsINNER JOIN
    dbo.InvMove ON dbo.GrnDetails.StockCode =
    dbo.InvMove.StockCode
    WHERE ???????

    where should I be referencing the INSERTED temp table?


    "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    news:u59y8h9PDHA.2852tk2msftngp13.phx.gbl...
    > Make use of INSERTED temporary table,the inserted table stores copies of
    the
    > affected rows during INSERT. this table is visible to trigger body only.
    ie
    > if you are writing a trigger FOR INSERT operation, you can refer to this
    > table inside the trigger body.
    > See BOL for more information.
    >
    > --
    > -Vishal
    > "Newbie" <noideanospam.com> wrote in message
    > news:eJL1Se9PDHA.3016TK2MSFTNGP10.phx.gbl...
    > > Hi
    > >
    > > I have the following tables TranDetails (3rd Party) with 4 fields making
    > up
    > > primary key
    > > Supplier char 7
    > > GRN char 7
    > > Journal decimal
    > > JournalEntry decimal
    > > detail1
    > > detail1
    > > detail3
    > >
    > > InvMove
    > > Journal (PK) decimal
    > > JournalEntry (PK) decimal
    > > Stockcode char
    > > Supplier char
    > > Date date
    > >
    > >
    > > GRNDetails
    > > GRN
    > > Supplier
    > > Stockcode
    > > etc
    > >
    > >
    > > when a record is inserted into the TranDetails table I would like to
    > insert
    > > a record into GRNDetails table based on a query from the InvMove and
    > > TransDetails table.
    > > I want the query to be limited to the record that has just been entered
    > into
    > > the TranDetails table i.e. the query would only ever return one record
    > >
    > > How would I do this using a trigger? I am a complete beginner where
    this
    > is
    > > concerned so any help would be greatly appreciated
    > >
    > >
    >
    >

    Newbie Guest

  4. #4

    Default Re: trigger question

    Thanks but I'm still confused . . . . . :-(

    where does the InvMove table come into the query?
    does the inserted temp table just replace the references to the TranDetails
    table?

    i.e

    SELECT a.Supplier, a.Grn,
    a.OrigReceiptDate
    FROM inserted a INNER JOIN
    dbo.InvMove b ON a.StockCode =
    b.StockCode


    "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    news:#g2e5K#PDHA.2244TK2MSFTNGP11.phx.gbl...
    > INSERTED table will have same table design as triggering table ie
    > TranDetails (same columnname/datatype) hence you can rewrite your query
    as
    >
    > SELECT a.Supplier, a.Grn,
    > a.OrigReceiptDate
    > FROM dbo.TranDetails a INNER JOIN inserted b on
    > a.stockcode = b.stockcode
    > where .....
    >
    > --
    > -Vishal
    > "Newbie" <noideanospam.com> wrote in message
    > news:e2h7tE#PDHA.1748TK2MSFTNGP11.phx.gbl...
    > > Thanks for that but I am not sure understand what you mean . . . ..
    > >
    > > Would I need to do something like:
    > >
    > > CREATE TRIGGER newrecord
    > > ON TranDetails
    > > FOR INSERT
    > > AS
    > > SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    > > dbo.TranDetails.OrigReceiptDate
    > > FROM dbo.TranDetailsINNER JOIN
    > > dbo.InvMove ON dbo.GrnDetails.StockCode =
    > > dbo.InvMove.StockCode
    > > WHERE ???????
    > >
    > > where should I be referencing the INSERTED temp table?
    > >
    > >
    > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > news:u59y8h9PDHA.2852tk2msftngp13.phx.gbl...
    > > > Make use of INSERTED temporary table,the inserted table stores copies
    of
    > > the
    > > > affected rows during INSERT. this table is visible to trigger body
    only.
    > > ie
    > > > if you are writing a trigger FOR INSERT operation, you can refer to
    this
    > > > table inside the trigger body.
    > > > See BOL for more information.
    > > >
    > > > --
    > > > -Vishal
    > > > "Newbie" <noideanospam.com> wrote in message
    > > > news:eJL1Se9PDHA.3016TK2MSFTNGP10.phx.gbl...
    > > > > Hi
    > > > >
    > > > > I have the following tables TranDetails (3rd Party) with 4 fields
    > making
    > > > up
    > > > > primary key
    > > > > Supplier char 7
    > > > > GRN char 7
    > > > > Journal decimal
    > > > > JournalEntry decimal
    > > > > detail1
    > > > > detail1
    > > > > detail3
    > > > >
    > > > > InvMove
    > > > > Journal (PK) decimal
    > > > > JournalEntry (PK) decimal
    > > > > Stockcode char
    > > > > Supplier char
    > > > > Date date
    > > > >
    > > > >
    > > > > GRNDetails
    > > > > GRN
    > > > > Supplier
    > > > > Stockcode
    > > > > etc
    > > > >
    > > > >
    > > > > when a record is inserted into the TranDetails table I would like to
    > > > insert
    > > > > a record into GRNDetails table based on a query from the InvMove and
    > > > > TransDetails table.
    > > > > I want the query to be limited to the record that has just been
    > entered
    > > > into
    > > > > the TranDetails table i.e. the query would only ever return one
    record
    > > > >
    > > > > How would I do this using a trigger? I am a complete beginner where
    > > this
    > > > is
    > > > > concerned so any help would be greatly appreciated
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Newbie Guest

  5. #5

    Default Re: trigger question

    are you trying to insert the records into inmove table? if yes then the
    syntax would be.

    insert into invmove(col1,col2,col2)
    SELECT a.Supplier, a.Grn,
    a.OrigReceiptDate
    FROM dbo.TranDetails a INNER JOIN inserted b on
    a.stockcode = b.stockcode
    where .....

    --
    -Vishal
    "Newbie" <noideanospam.com> wrote in message
    news:esX$pQ#PDHA.3880tk2msftngp13.phx.gbl...
    > Thanks but I'm still confused . . . . . :-(
    >
    > where does the InvMove table come into the query?
    > does the inserted temp table just replace the references to the
    TranDetails
    > table?
    >
    > i.e
    >
    > SELECT a.Supplier, a.Grn,
    > a.OrigReceiptDate
    > FROM inserted a INNER JOIN
    > dbo.InvMove b ON a.StockCode =
    > b.StockCode
    >
    >
    > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > news:#g2e5K#PDHA.2244TK2MSFTNGP11.phx.gbl...
    > > INSERTED table will have same table design as triggering table ie
    > > TranDetails (same columnname/datatype) hence you can rewrite your query
    > as
    > >
    > > SELECT a.Supplier, a.Grn,
    > > a.OrigReceiptDate
    > > FROM dbo.TranDetails a INNER JOIN inserted b on
    > > a.stockcode = b.stockcode
    > > where .....
    > >
    > > --
    > > -Vishal
    > > "Newbie" <noideanospam.com> wrote in message
    > > news:e2h7tE#PDHA.1748TK2MSFTNGP11.phx.gbl...
    > > > Thanks for that but I am not sure understand what you mean . . . ..
    > > >
    > > > Would I need to do something like:
    > > >
    > > > CREATE TRIGGER newrecord
    > > > ON TranDetails
    > > > FOR INSERT
    > > > AS
    > > > SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    > > > dbo.TranDetails.OrigReceiptDate
    > > > FROM dbo.TranDetailsINNER JOIN
    > > > dbo.InvMove ON dbo.GrnDetails.StockCode =
    > > > dbo.InvMove.StockCode
    > > > WHERE ???????
    > > >
    > > > where should I be referencing the INSERTED temp table?
    > > >
    > > >
    > > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > > news:u59y8h9PDHA.2852tk2msftngp13.phx.gbl...
    > > > > Make use of INSERTED temporary table,the inserted table stores
    copies
    > of
    > > > the
    > > > > affected rows during INSERT. this table is visible to trigger body
    > only.
    > > > ie
    > > > > if you are writing a trigger FOR INSERT operation, you can refer to
    > this
    > > > > table inside the trigger body.
    > > > > See BOL for more information.
    > > > >
    > > > > --
    > > > > -Vishal
    > > > > "Newbie" <noideanospam.com> wrote in message
    > > > > news:eJL1Se9PDHA.3016TK2MSFTNGP10.phx.gbl...
    > > > > > Hi
    > > > > >
    > > > > > I have the following tables TranDetails (3rd Party) with 4 fields
    > > making
    > > > > up
    > > > > > primary key
    > > > > > Supplier char 7
    > > > > > GRN char 7
    > > > > > Journal decimal
    > > > > > JournalEntry decimal
    > > > > > detail1
    > > > > > detail1
    > > > > > detail3
    > > > > >
    > > > > > InvMove
    > > > > > Journal (PK) decimal
    > > > > > JournalEntry (PK) decimal
    > > > > > Stockcode char
    > > > > > Supplier char
    > > > > > Date date
    > > > > >
    > > > > >
    > > > > > GRNDetails
    > > > > > GRN
    > > > > > Supplier
    > > > > > Stockcode
    > > > > > etc
    > > > > >
    > > > > >
    > > > > > when a record is inserted into the TranDetails table I would like
    to
    > > > > insert
    > > > > > a record into GRNDetails table based on a query from the InvMove
    and
    > > > > > TransDetails table.
    > > > > > I want the query to be limited to the record that has just been
    > > entered
    > > > > into
    > > > > > the TranDetails table i.e. the query would only ever return one
    > record
    > > > > >
    > > > > > How would I do this using a trigger? I am a complete beginner
    where
    > > > this
    > > > > is
    > > > > > concerned so any help would be greatly appreciated
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Vishal Parkar Guest

  6. #6

    Default Re: trigger question

    no I want to insert into table RECEIPTS (this syntax wasn't included before)
    the results of the query

    SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    dbo.TranDetails.OrigReceiptDate
    FROM dbo.TranDetailsINNER JOIN
    dbo.InvMove ON dbo.TranDetails.StockCode =
    dbo.InvMove.StockCode
    WHERE . . . the Grn = the Grn of the record that has just been inserted
    into the TranDetails table



    "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    news:OyNGYT#PDHA.3144tk2msftngp13.phx.gbl...
    > are you trying to insert the records into inmove table? if yes then the
    > syntax would be.
    >
    > insert into invmove(col1,col2,col2)
    > SELECT a.Supplier, a.Grn,
    > a.OrigReceiptDate
    > FROM dbo.TranDetails a INNER JOIN inserted b on
    > a.stockcode = b.stockcode
    > where .....
    >
    > --
    > -Vishal
    > "Newbie" <noideanospam.com> wrote in message
    > news:esX$pQ#PDHA.3880tk2msftngp13.phx.gbl...
    > > Thanks but I'm still confused . . . . . :-(
    > >
    > > where does the InvMove table come into the query?
    > > does the inserted temp table just replace the references to the
    > TranDetails
    > > table?
    > >
    > > i.e
    > >
    > > SELECT a.Supplier, a.Grn,
    > > a.OrigReceiptDate
    > > FROM inserted a INNER JOIN
    > > dbo.InvMove b ON a.StockCode =
    > > b.StockCode
    > >
    > >
    > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > news:#g2e5K#PDHA.2244TK2MSFTNGP11.phx.gbl...
    > > > INSERTED table will have same table design as triggering table ie
    > > > TranDetails (same columnname/datatype) hence you can rewrite your
    query
    > > as
    > > >
    > > > SELECT a.Supplier, a.Grn,
    > > > a.OrigReceiptDate
    > > > FROM dbo.TranDetails a INNER JOIN inserted b on
    > > > a.stockcode = b.stockcode
    > > > where .....
    > > >
    > > > --
    > > > -Vishal
    > > > "Newbie" <noideanospam.com> wrote in message
    > > > news:e2h7tE#PDHA.1748TK2MSFTNGP11.phx.gbl...
    > > > > Thanks for that but I am not sure understand what you mean . . .
    ...
    > > > >
    > > > > Would I need to do something like:
    > > > >
    > > > > CREATE TRIGGER newrecord
    > > > > ON TranDetails
    > > > > FOR INSERT
    > > > > AS
    > > > > SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    > > > > dbo.TranDetails.OrigReceiptDate
    > > > > FROM dbo.TranDetailsINNER JOIN
    > > > > dbo.InvMove ON dbo.GrnDetails.StockCode =
    > > > > dbo.InvMove.StockCode
    > > > > WHERE ???????
    > > > >
    > > > > where should I be referencing the INSERTED temp table?
    > > > >
    > > > >
    > > > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > > > news:u59y8h9PDHA.2852tk2msftngp13.phx.gbl...
    > > > > > Make use of INSERTED temporary table,the inserted table stores
    > copies
    > > of
    > > > > the
    > > > > > affected rows during INSERT. this table is visible to trigger body
    > > only.
    > > > > ie
    > > > > > if you are writing a trigger FOR INSERT operation, you can refer
    to
    > > this
    > > > > > table inside the trigger body.
    > > > > > See BOL for more information.
    > > > > >
    > > > > > --
    > > > > > -Vishal
    > > > > > "Newbie" <noideanospam.com> wrote in message
    > > > > > news:eJL1Se9PDHA.3016TK2MSFTNGP10.phx.gbl...
    > > > > > > Hi
    > > > > > >
    > > > > > > I have the following tables TranDetails (3rd Party) with 4
    fields
    > > > making
    > > > > > up
    > > > > > > primary key
    > > > > > > Supplier char 7
    > > > > > > GRN char 7
    > > > > > > Journal decimal
    > > > > > > JournalEntry decimal
    > > > > > > detail1
    > > > > > > detail1
    > > > > > > detail3
    > > > > > >
    > > > > > > InvMove
    > > > > > > Journal (PK) decimal
    > > > > > > JournalEntry (PK) decimal
    > > > > > > Stockcode char
    > > > > > > Supplier char
    > > > > > > Date date
    > > > > > >
    > > > > > >
    > > > > > > GRNDetails
    > > > > > > GRN
    > > > > > > Supplier
    > > > > > > Stockcode
    > > > > > > etc
    > > > > > >
    > > > > > >
    > > > > > > when a record is inserted into the TranDetails table I would
    like
    > to
    > > > > > insert
    > > > > > > a record into GRNDetails table based on a query from the InvMove
    > and
    > > > > > > TransDetails table.
    > > > > > > I want the query to be limited to the record that has just been
    > > > entered
    > > > > > into
    > > > > > > the TranDetails table i.e. the query would only ever return one
    > > record
    > > > > > >
    > > > > > > How would I do this using a trigger? I am a complete beginner
    > where
    > > > > this
    > > > > > is
    > > > > > > concerned so any help would be greatly appreciated
    > > > > > >
    > > > > > >
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Newbie Guest

  7. #7

    Default Re: trigger question

    i assume you want to add INSERTED table as a join in the query. make sure
    you are using proper fieldnames in join clause.

    try:

    SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    dbo.TranDetails.OrigReceiptDate
    FROM dbo.TranDetailsINNER JOIN
    dbo.InvMove ON dbo.TranDetails.StockCode =
    dbo.InvMove.StockCode
    WHERE dbo.TranDetails.Grn in
    (select grn from inserted)

    OR

    SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    dbo.TranDetails.OrigReceiptDate
    FROM dbo.TranDetails INNER JOIN
    dbo.InvMove ON dbo.TranDetails.StockCode =
    dbo.InvMove.StockCode inner join inserted on
    inserted.grn = dbo.trandetails.grn


    --
    -Vishal
    "Newbie" <noideanospam.com> wrote in message
    news:#4bx4Y#PDHA.2768tk2msftngp13.phx.gbl...
    > no I want to insert into table RECEIPTS (this syntax wasn't included
    before)
    > the results of the query
    >
    > SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    > dbo.TranDetails.OrigReceiptDate
    > FROM dbo.TranDetailsINNER JOIN
    > dbo.InvMove ON dbo.TranDetails.StockCode =
    > dbo.InvMove.StockCode
    > WHERE . . . the Grn = the Grn of the record that has just been
    inserted
    > into the TranDetails table
    >
    >
    >
    > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > news:OyNGYT#PDHA.3144tk2msftngp13.phx.gbl...
    > > are you trying to insert the records into inmove table? if yes then the
    > > syntax would be.
    > >
    > > insert into invmove(col1,col2,col2)
    > > SELECT a.Supplier, a.Grn,
    > > a.OrigReceiptDate
    > > FROM dbo.TranDetails a INNER JOIN inserted b on
    > > a.stockcode = b.stockcode
    > > where .....
    > >
    > > --
    > > -Vishal
    > > "Newbie" <noideanospam.com> wrote in message
    > > news:esX$pQ#PDHA.3880tk2msftngp13.phx.gbl...
    > > > Thanks but I'm still confused . . . . . :-(
    > > >
    > > > where does the InvMove table come into the query?
    > > > does the inserted temp table just replace the references to the
    > > TranDetails
    > > > table?
    > > >
    > > > i.e
    > > >
    > > > SELECT a.Supplier, a.Grn,
    > > > a.OrigReceiptDate
    > > > FROM inserted a INNER JOIN
    > > > dbo.InvMove b ON a.StockCode =
    > > > b.StockCode
    > > >
    > > >
    > > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > > news:#g2e5K#PDHA.2244TK2MSFTNGP11.phx.gbl...
    > > > > INSERTED table will have same table design as triggering table ie
    > > > > TranDetails (same columnname/datatype) hence you can rewrite your
    > query
    > > > as
    > > > >
    > > > > SELECT a.Supplier, a.Grn,
    > > > > a.OrigReceiptDate
    > > > > FROM dbo.TranDetails a INNER JOIN inserted b on
    > > > > a.stockcode = b.stockcode
    > > > > where .....
    > > > >
    > > > > --
    > > > > -Vishal
    > > > > "Newbie" <noideanospam.com> wrote in message
    > > > > news:e2h7tE#PDHA.1748TK2MSFTNGP11.phx.gbl...
    > > > > > Thanks for that but I am not sure understand what you mean . . .
    > ..
    > > > > >
    > > > > > Would I need to do something like:
    > > > > >
    > > > > > CREATE TRIGGER newrecord
    > > > > > ON TranDetails
    > > > > > FOR INSERT
    > > > > > AS
    > > > > > SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    > > > > > dbo.TranDetails.OrigReceiptDate
    > > > > > FROM dbo.TranDetailsINNER JOIN
    > > > > > dbo.InvMove ON dbo.GrnDetails.StockCode =
    > > > > > dbo.InvMove.StockCode
    > > > > > WHERE ???????
    > > > > >
    > > > > > where should I be referencing the INSERTED temp table?
    > > > > >
    > > > > >
    > > > > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > > > > news:u59y8h9PDHA.2852tk2msftngp13.phx.gbl...
    > > > > > > Make use of INSERTED temporary table,the inserted table stores
    > > copies
    > > > of
    > > > > > the
    > > > > > > affected rows during INSERT. this table is visible to trigger
    body
    > > > only.
    > > > > > ie
    > > > > > > if you are writing a trigger FOR INSERT operation, you can refer
    > to
    > > > this
    > > > > > > table inside the trigger body.
    > > > > > > See BOL for more information.
    > > > > > >
    > > > > > > --
    > > > > > > -Vishal
    > > > > > > "Newbie" <noideanospam.com> wrote in message
    > > > > > > news:eJL1Se9PDHA.3016TK2MSFTNGP10.phx.gbl...
    > > > > > > > Hi
    > > > > > > >
    > > > > > > > I have the following tables TranDetails (3rd Party) with 4
    > fields
    > > > > making
    > > > > > > up
    > > > > > > > primary key
    > > > > > > > Supplier char 7
    > > > > > > > GRN char 7
    > > > > > > > Journal decimal
    > > > > > > > JournalEntry decimal
    > > > > > > > detail1
    > > > > > > > detail1
    > > > > > > > detail3
    > > > > > > >
    > > > > > > > InvMove
    > > > > > > > Journal (PK) decimal
    > > > > > > > JournalEntry (PK) decimal
    > > > > > > > Stockcode char
    > > > > > > > Supplier char
    > > > > > > > Date date
    > > > > > > >
    > > > > > > >
    > > > > > > > GRNDetails
    > > > > > > > GRN
    > > > > > > > Supplier
    > > > > > > > Stockcode
    > > > > > > > etc
    > > > > > > >
    > > > > > > >
    > > > > > > > when a record is inserted into the TranDetails table I would
    > like
    > > to
    > > > > > > insert
    > > > > > > > a record into GRNDetails table based on a query from the
    InvMove
    > > and
    > > > > > > > TransDetails table.
    > > > > > > > I want the query to be limited to the record that has just
    been
    > > > > entered
    > > > > > > into
    > > > > > > > the TranDetails table i.e. the query would only ever return
    one
    > > > record
    > > > > > > >
    > > > > > > > How would I do this using a trigger? I am a complete beginner
    > > where
    > > > > > this
    > > > > > > is
    > > > > > > > concerned so any help would be greatly appreciated
    > > > > > > >
    > > > > > > >
    > > > > > >
    > > > > > >
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Vishal Parkar Guest

  8. #8

    Default Re: trigger question

    Thanks that seems to work :-) one last question . . .

    If I wanted to limit the trigger to only inserting records into my table if
    the qty < 0 for the record in the inserted table what would I need to add?

    FYI I used the 1st option you gave using the subquery

    Thanks
    "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    news:efhJXe#PDHA.2052TK2MSFTNGP11.phx.gbl...
    > i assume you want to add INSERTED table as a join in the query. make sure
    > you are using proper fieldnames in join clause.
    >
    > try:
    >
    > SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    > dbo.TranDetails.OrigReceiptDate
    > FROM dbo.TranDetailsINNER JOIN
    > dbo.InvMove ON dbo.TranDetails.StockCode =
    > dbo.InvMove.StockCode
    > WHERE dbo.TranDetails.Grn in
    > (select grn from inserted)
    >
    > OR
    >
    > SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    > dbo.TranDetails.OrigReceiptDate
    > FROM dbo.TranDetails INNER JOIN
    > dbo.InvMove ON dbo.TranDetails.StockCode =
    > dbo.InvMove.StockCode inner join inserted on
    > inserted.grn = dbo.trandetails.grn
    >
    >
    > --
    > -Vishal
    > "Newbie" <noideanospam.com> wrote in message
    > news:#4bx4Y#PDHA.2768tk2msftngp13.phx.gbl...
    > > no I want to insert into table RECEIPTS (this syntax wasn't included
    > before)
    > > the results of the query
    > >
    > > SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    > > dbo.TranDetails.OrigReceiptDate
    > > FROM dbo.TranDetailsINNER JOIN
    > > dbo.InvMove ON dbo.TranDetails.StockCode =
    > > dbo.InvMove.StockCode
    > > WHERE . . . the Grn = the Grn of the record that has just been
    > inserted
    > > into the TranDetails table
    > >
    > >
    > >
    > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > news:OyNGYT#PDHA.3144tk2msftngp13.phx.gbl...
    > > > are you trying to insert the records into inmove table? if yes then
    the
    > > > syntax would be.
    > > >
    > > > insert into invmove(col1,col2,col2)
    > > > SELECT a.Supplier, a.Grn,
    > > > a.OrigReceiptDate
    > > > FROM dbo.TranDetails a INNER JOIN inserted b on
    > > > a.stockcode = b.stockcode
    > > > where .....
    > > >
    > > > --
    > > > -Vishal
    > > > "Newbie" <noideanospam.com> wrote in message
    > > > news:esX$pQ#PDHA.3880tk2msftngp13.phx.gbl...
    > > > > Thanks but I'm still confused . . . . . :-(
    > > > >
    > > > > where does the InvMove table come into the query?
    > > > > does the inserted temp table just replace the references to the
    > > > TranDetails
    > > > > table?
    > > > >
    > > > > i.e
    > > > >
    > > > > SELECT a.Supplier, a.Grn,
    > > > > a.OrigReceiptDate
    > > > > FROM inserted a INNER JOIN
    > > > > dbo.InvMove b ON a.StockCode =
    > > > > b.StockCode
    > > > >
    > > > >
    > > > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > > > news:#g2e5K#PDHA.2244TK2MSFTNGP11.phx.gbl...
    > > > > > INSERTED table will have same table design as triggering table ie
    > > > > > TranDetails (same columnname/datatype) hence you can rewrite your
    > > query
    > > > > as
    > > > > >
    > > > > > SELECT a.Supplier, a.Grn,
    > > > > > a.OrigReceiptDate
    > > > > > FROM dbo.TranDetails a INNER JOIN inserted b on
    > > > > > a.stockcode = b.stockcode
    > > > > > where .....
    > > > > >
    > > > > > --
    > > > > > -Vishal
    > > > > > "Newbie" <noideanospam.com> wrote in message
    > > > > > news:e2h7tE#PDHA.1748TK2MSFTNGP11.phx.gbl...
    > > > > > > Thanks for that but I am not sure understand what you mean . .
    ..
    > > ..
    > > > > > >
    > > > > > > Would I need to do something like:
    > > > > > >
    > > > > > > CREATE TRIGGER newrecord
    > > > > > > ON TranDetails
    > > > > > > FOR INSERT
    > > > > > > AS
    > > > > > > SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    > > > > > > dbo.TranDetails.OrigReceiptDate
    > > > > > > FROM dbo.TranDetailsINNER JOIN
    > > > > > > dbo.InvMove ON dbo.GrnDetails.StockCode =
    > > > > > > dbo.InvMove.StockCode
    > > > > > > WHERE ???????
    > > > > > >
    > > > > > > where should I be referencing the INSERTED temp table?
    > > > > > >
    > > > > > >
    > > > > > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > > > > > news:u59y8h9PDHA.2852tk2msftngp13.phx.gbl...
    > > > > > > > Make use of INSERTED temporary table,the inserted table stores
    > > > copies
    > > > > of
    > > > > > > the
    > > > > > > > affected rows during INSERT. this table is visible to trigger
    > body
    > > > > only.
    > > > > > > ie
    > > > > > > > if you are writing a trigger FOR INSERT operation, you can
    refer
    > > to
    > > > > this
    > > > > > > > table inside the trigger body.
    > > > > > > > See BOL for more information.
    > > > > > > >
    > > > > > > > --
    > > > > > > > -Vishal
    > > > > > > > "Newbie" <noideanospam.com> wrote in message
    > > > > > > > news:eJL1Se9PDHA.3016TK2MSFTNGP10.phx.gbl...
    > > > > > > > > Hi
    > > > > > > > >
    > > > > > > > > I have the following tables TranDetails (3rd Party) with 4
    > > fields
    > > > > > making
    > > > > > > > up
    > > > > > > > > primary key
    > > > > > > > > Supplier char 7
    > > > > > > > > GRN char 7
    > > > > > > > > Journal decimal
    > > > > > > > > JournalEntry decimal
    > > > > > > > > detail1
    > > > > > > > > detail1
    > > > > > > > > detail3
    > > > > > > > >
    > > > > > > > > InvMove
    > > > > > > > > Journal (PK) decimal
    > > > > > > > > JournalEntry (PK) decimal
    > > > > > > > > Stockcode char
    > > > > > > > > Supplier char
    > > > > > > > > Date date
    > > > > > > > >
    > > > > > > > >
    > > > > > > > > GRNDetails
    > > > > > > > > GRN
    > > > > > > > > Supplier
    > > > > > > > > Stockcode
    > > > > > > > > etc
    > > > > > > > >
    > > > > > > > >
    > > > > > > > > when a record is inserted into the TranDetails table I would
    > > like
    > > > to
    > > > > > > > insert
    > > > > > > > > a record into GRNDetails table based on a query from the
    > InvMove
    > > > and
    > > > > > > > > TransDetails table.
    > > > > > > > > I want the query to be limited to the record that has just
    > been
    > > > > > entered
    > > > > > > > into
    > > > > > > > > the TranDetails table i.e. the query would only ever return
    > one
    > > > > record
    > > > > > > > >
    > > > > > > > > How would I do this using a trigger? I am a complete
    beginner
    > > > where
    > > > > > > this
    > > > > > > > is
    > > > > > > > > concerned so any help would be greatly appreciated
    > > > > > > > >
    > > > > > > > >
    > > > > > > >
    > > > > > > >
    > > > > > >
    > > > > > >
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Newbie Guest

  9. #9

    Default Re: trigger question

    add where cluase to the query
    ex:
    SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    dbo.TranDetails.OrigReceiptDate
    FROM dbo.TranDetails INNER JOIN
    dbo.InvMove ON dbo.TranDetails.StockCode =
    dbo.InvMove.StockCode inner join inserted on
    inserted.grn = dbo.trandetails.grn
    where inserted.qty < 0--check this condition and use right column name.

    --
    -Vishal
    "Newbie" <noideanospam.com> wrote in message
    news:eNG5Sd$PDHA.3144tk2msftngp13.phx.gbl...
    > Thanks that seems to work :-) one last question . . .
    >
    > If I wanted to limit the trigger to only inserting records into my table
    if
    > the qty < 0 for the record in the inserted table what would I need to
    add?
    >
    > FYI I used the 1st option you gave using the subquery
    >
    > Thanks
    > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > news:efhJXe#PDHA.2052TK2MSFTNGP11.phx.gbl...
    > > i assume you want to add INSERTED table as a join in the query. make
    sure
    > > you are using proper fieldnames in join clause.
    > >
    > > try:
    > >
    > > SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    > > dbo.TranDetails.OrigReceiptDate
    > > FROM dbo.TranDetailsINNER JOIN
    > > dbo.InvMove ON dbo.TranDetails.StockCode =
    > > dbo.InvMove.StockCode
    > > WHERE dbo.TranDetails.Grn in
    > > (select grn from inserted)
    > >
    > > OR
    > >
    > > SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    > > dbo.TranDetails.OrigReceiptDate
    > > FROM dbo.TranDetails INNER JOIN
    > > dbo.InvMove ON dbo.TranDetails.StockCode =
    > > dbo.InvMove.StockCode inner join inserted on
    > > inserted.grn = dbo.trandetails.grn
    > >
    > >
    > > --
    > > -Vishal
    > > "Newbie" <noideanospam.com> wrote in message
    > > news:#4bx4Y#PDHA.2768tk2msftngp13.phx.gbl...
    > > > no I want to insert into table RECEIPTS (this syntax wasn't included
    > > before)
    > > > the results of the query
    > > >
    > > > SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    > > > dbo.TranDetails.OrigReceiptDate
    > > > FROM dbo.TranDetailsINNER JOIN
    > > > dbo.InvMove ON dbo.TranDetails.StockCode =
    > > > dbo.InvMove.StockCode
    > > > WHERE . . . the Grn = the Grn of the record that has just been
    > > inserted
    > > > into the TranDetails table
    > > >
    > > >
    > > >
    > > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > > news:OyNGYT#PDHA.3144tk2msftngp13.phx.gbl...
    > > > > are you trying to insert the records into inmove table? if yes then
    > the
    > > > > syntax would be.
    > > > >
    > > > > insert into invmove(col1,col2,col2)
    > > > > SELECT a.Supplier, a.Grn,
    > > > > a.OrigReceiptDate
    > > > > FROM dbo.TranDetails a INNER JOIN inserted b on
    > > > > a.stockcode = b.stockcode
    > > > > where .....
    > > > >
    > > > > --
    > > > > -Vishal
    > > > > "Newbie" <noideanospam.com> wrote in message
    > > > > news:esX$pQ#PDHA.3880tk2msftngp13.phx.gbl...
    > > > > > Thanks but I'm still confused . . . . . :-(
    > > > > >
    > > > > > where does the InvMove table come into the query?
    > > > > > does the inserted temp table just replace the references to the
    > > > > TranDetails
    > > > > > table?
    > > > > >
    > > > > > i.e
    > > > > >
    > > > > > SELECT a.Supplier, a.Grn,
    > > > > > a.OrigReceiptDate
    > > > > > FROM inserted a INNER JOIN
    > > > > > dbo.InvMove b ON a.StockCode =
    > > > > > b.StockCode
    > > > > >
    > > > > >
    > > > > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > > > > news:#g2e5K#PDHA.2244TK2MSFTNGP11.phx.gbl...
    > > > > > > INSERTED table will have same table design as triggering table
    ie
    > > > > > > TranDetails (same columnname/datatype) hence you can rewrite
    your
    > > > query
    > > > > > as
    > > > > > >
    > > > > > > SELECT a.Supplier, a.Grn,
    > > > > > > a.OrigReceiptDate
    > > > > > > FROM dbo.TranDetails a INNER JOIN inserted b on
    > > > > > > a.stockcode = b.stockcode
    > > > > > > where .....
    > > > > > >
    > > > > > > --
    > > > > > > -Vishal
    > > > > > > "Newbie" <noideanospam.com> wrote in message
    > > > > > > news:e2h7tE#PDHA.1748TK2MSFTNGP11.phx.gbl...
    > > > > > > > Thanks for that but I am not sure understand what you mean .
    ..
    > .
    > > > ..
    > > > > > > >
    > > > > > > > Would I need to do something like:
    > > > > > > >
    > > > > > > > CREATE TRIGGER newrecord
    > > > > > > > ON TranDetails
    > > > > > > > FOR INSERT
    > > > > > > > AS
    > > > > > > > SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    > > > > > > > dbo.TranDetails.OrigReceiptDate
    > > > > > > > FROM dbo.TranDetailsINNER JOIN
    > > > > > > > dbo.InvMove ON dbo.GrnDetails.StockCode =
    > > > > > > > dbo.InvMove.StockCode
    > > > > > > > WHERE ???????
    > > > > > > >
    > > > > > > > where should I be referencing the INSERTED temp table?
    > > > > > > >
    > > > > > > >
    > > > > > > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > > > > > > news:u59y8h9PDHA.2852tk2msftngp13.phx.gbl...
    > > > > > > > > Make use of INSERTED temporary table,the inserted table
    stores
    > > > > copies
    > > > > > of
    > > > > > > > the
    > > > > > > > > affected rows during INSERT. this table is visible to
    trigger
    > > body
    > > > > > only.
    > > > > > > > ie
    > > > > > > > > if you are writing a trigger FOR INSERT operation, you can
    > refer
    > > > to
    > > > > > this
    > > > > > > > > table inside the trigger body.
    > > > > > > > > See BOL for more information.
    > > > > > > > >
    > > > > > > > > --
    > > > > > > > > -Vishal
    > > > > > > > > "Newbie" <noideanospam.com> wrote in message
    > > > > > > > > news:eJL1Se9PDHA.3016TK2MSFTNGP10.phx.gbl...
    > > > > > > > > > Hi
    > > > > > > > > >
    > > > > > > > > > I have the following tables TranDetails (3rd Party) with 4
    > > > fields
    > > > > > > making
    > > > > > > > > up
    > > > > > > > > > primary key
    > > > > > > > > > Supplier char 7
    > > > > > > > > > GRN char 7
    > > > > > > > > > Journal decimal
    > > > > > > > > > JournalEntry decimal
    > > > > > > > > > detail1
    > > > > > > > > > detail1
    > > > > > > > > > detail3
    > > > > > > > > >
    > > > > > > > > > InvMove
    > > > > > > > > > Journal (PK) decimal
    > > > > > > > > > JournalEntry (PK) decimal
    > > > > > > > > > Stockcode char
    > > > > > > > > > Supplier char
    > > > > > > > > > Date date
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > > GRNDetails
    > > > > > > > > > GRN
    > > > > > > > > > Supplier
    > > > > > > > > > Stockcode
    > > > > > > > > > etc
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > > when a record is inserted into the TranDetails table I
    would
    > > > like
    > > > > to
    > > > > > > > > insert
    > > > > > > > > > a record into GRNDetails table based on a query from the
    > > InvMove
    > > > > and
    > > > > > > > > > TransDetails table.
    > > > > > > > > > I want the query to be limited to the record that has just
    > > been
    > > > > > > entered
    > > > > > > > > into
    > > > > > > > > > the TranDetails table i.e. the query would only ever
    return
    > > one
    > > > > > record
    > > > > > > > > >
    > > > > > > > > > How would I do this using a trigger? I am a complete
    > beginner
    > > > > where
    > > > > > > > this
    > > > > > > > > is
    > > > > > > > > > concerned so any help would be greatly appreciated
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > >
    > > > > > > > >
    > > > > > > >
    > > > > > > >
    > > > > > >
    > > > > > >
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Vishal Parkar Guest

  10. #10

    Default Re: trigger question

    Thanks for your help but I couldn't get this to work

    Here is what I have so far but this puts records in the table without any
    regard to qty - I want to be able to limit it to negative records

    What do I do ?


    CREATE TRIGGER receipts ON[dbo].[GrnDetails]
    FOR INSERT
    AS
    insert into RejectTrigger(GRD.Supplier,
    GRD.Grn,
    GRD.PurchaseOrder,
    GRD.PurchaseOrderLin,
    GRD.StockCode,
    GRD.QtyReceived,
    GRD.QtyUom,
    GRD.DeliveryNote,
    GRD.OrigReceiptDate,
    INM.LongDesc)
    SELECT DISTINCT
    GRD.Supplier,
    GRD.Grn,
    GRD.PurchaseOrder,
    GRD.PurchaseOrderLin,
    GRD.StockCode,
    GRD.QtyReceived,
    GRD.QtyUom,
    GRD.DeliveryNote,
    GRD.OrigReceiptDate,
    INM.LongDesc


    FROM dbo.GrnDetails GRD INNER JOIN

    dbo.ApSupplier APM
    ON GRD.Supplier = APM.Supplier INNER JOIN

    dbo.ApSupplierAddr APA
    ON APM.Supplier = APA.Supplier LEFT OUTER JOIN

    dbo.InvJournalDet IJD
    ON GRD.Journal = IJD.Journal AND
    GRD.JournalEntry = IJD.EntryNumber LEFT OUTER JOIN

    dbo.InvMaster INM
    ON GRD.StockCode = INM.StockCode LEFT OUTER JOIN

    dbo.InvSerialTrn IST
    ON GRD.Grn = IST.Reference
    Where GRD.Grn in
    (select distinct inserted.Grn from inserted )


    "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    news:e0b86m$PDHA.2052TK2MSFTNGP11.phx.gbl...
    > add where cluase to the query
    > ex:
    > SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    > dbo.TranDetails.OrigReceiptDate
    > FROM dbo.TranDetails INNER JOIN
    > dbo.InvMove ON dbo.TranDetails.StockCode =
    > dbo.InvMove.StockCode inner join inserted on
    > inserted.grn = dbo.trandetails.grn
    > where inserted.qty < 0--check this condition and use right column name.
    >
    > --
    > -Vishal
    > "Newbie" <noideanospam.com> wrote in message
    > news:eNG5Sd$PDHA.3144tk2msftngp13.phx.gbl...
    > > Thanks that seems to work :-) one last question . . .
    > >
    > > If I wanted to limit the trigger to only inserting records into my table
    > if
    > > the qty < 0 for the record in the inserted table what would I need to
    > add?
    > >
    > > FYI I used the 1st option you gave using the subquery
    > >
    > > Thanks
    > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > news:efhJXe#PDHA.2052TK2MSFTNGP11.phx.gbl...
    > > > i assume you want to add INSERTED table as a join in the query. make
    > sure
    > > > you are using proper fieldnames in join clause.
    > > >
    > > > try:
    > > >
    > > > SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    > > > dbo.TranDetails.OrigReceiptDate
    > > > FROM dbo.TranDetailsINNER JOIN
    > > > dbo.InvMove ON dbo.TranDetails.StockCode =
    > > > dbo.InvMove.StockCode
    > > > WHERE dbo.TranDetails.Grn in
    > > > (select grn from inserted)
    > > >
    > > > OR
    > > >
    > > > SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    > > > dbo.TranDetails.OrigReceiptDate
    > > > FROM dbo.TranDetails INNER JOIN
    > > > dbo.InvMove ON dbo.TranDetails.StockCode =
    > > > dbo.InvMove.StockCode inner join inserted on
    > > > inserted.grn = dbo.trandetails.grn
    > > >
    > > >
    > > > --
    > > > -Vishal
    > > > "Newbie" <noideanospam.com> wrote in message
    > > > news:#4bx4Y#PDHA.2768tk2msftngp13.phx.gbl...
    > > > > no I want to insert into table RECEIPTS (this syntax wasn't included
    > > > before)
    > > > > the results of the query
    > > > >
    > > > > SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    > > > > dbo.TranDetails.OrigReceiptDate
    > > > > FROM dbo.TranDetailsINNER JOIN
    > > > > dbo.InvMove ON dbo.TranDetails.StockCode =
    > > > > dbo.InvMove.StockCode
    > > > > WHERE . . . the Grn = the Grn of the record that has just been
    > > > inserted
    > > > > into the TranDetails table
    > > > >
    > > > >
    > > > >
    > > > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > > > news:OyNGYT#PDHA.3144tk2msftngp13.phx.gbl...
    > > > > > are you trying to insert the records into inmove table? if yes
    then
    > > the
    > > > > > syntax would be.
    > > > > >
    > > > > > insert into invmove(col1,col2,col2)
    > > > > > SELECT a.Supplier, a.Grn,
    > > > > > a.OrigReceiptDate
    > > > > > FROM dbo.TranDetails a INNER JOIN inserted b on
    > > > > > a.stockcode = b.stockcode
    > > > > > where .....
    > > > > >
    > > > > > --
    > > > > > -Vishal
    > > > > > "Newbie" <noideanospam.com> wrote in message
    > > > > > news:esX$pQ#PDHA.3880tk2msftngp13.phx.gbl...
    > > > > > > Thanks but I'm still confused . . . . . :-(
    > > > > > >
    > > > > > > where does the InvMove table come into the query?
    > > > > > > does the inserted temp table just replace the references to the
    > > > > > TranDetails
    > > > > > > table?
    > > > > > >
    > > > > > > i.e
    > > > > > >
    > > > > > > SELECT a.Supplier, a.Grn,
    > > > > > > a.OrigReceiptDate
    > > > > > > FROM inserted a INNER JOIN
    > > > > > > dbo.InvMove b ON a.StockCode =
    > > > > > > b.StockCode
    > > > > > >
    > > > > > >
    > > > > > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > > > > > news:#g2e5K#PDHA.2244TK2MSFTNGP11.phx.gbl...
    > > > > > > > INSERTED table will have same table design as triggering
    table
    > ie
    > > > > > > > TranDetails (same columnname/datatype) hence you can rewrite
    > your
    > > > > query
    > > > > > > as
    > > > > > > >
    > > > > > > > SELECT a.Supplier, a.Grn,
    > > > > > > > a.OrigReceiptDate
    > > > > > > > FROM dbo.TranDetails a INNER JOIN inserted b on
    > > > > > > > a.stockcode = b.stockcode
    > > > > > > > where .....
    > > > > > > >
    > > > > > > > --
    > > > > > > > -Vishal
    > > > > > > > "Newbie" <noideanospam.com> wrote in message
    > > > > > > > news:e2h7tE#PDHA.1748TK2MSFTNGP11.phx.gbl...
    > > > > > > > > Thanks for that but I am not sure understand what you mean
    ..
    > .
    > > .
    > > > > ..
    > > > > > > > >
    > > > > > > > > Would I need to do something like:
    > > > > > > > >
    > > > > > > > > CREATE TRIGGER newrecord
    > > > > > > > > ON TranDetails
    > > > > > > > > FOR INSERT
    > > > > > > > > AS
    > > > > > > > > SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    > > > > > > > > dbo.TranDetails.OrigReceiptDate
    > > > > > > > > FROM dbo.TranDetailsINNER JOIN
    > > > > > > > > dbo.InvMove ON dbo.GrnDetails.StockCode =
    > > > > > > > > dbo.InvMove.StockCode
    > > > > > > > > WHERE ???????
    > > > > > > > >
    > > > > > > > > where should I be referencing the INSERTED temp table?
    > > > > > > > >
    > > > > > > > >
    > > > > > > > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > > > > > > > news:u59y8h9PDHA.2852tk2msftngp13.phx.gbl...
    > > > > > > > > > Make use of INSERTED temporary table,the inserted table
    > stores
    > > > > > copies
    > > > > > > of
    > > > > > > > > the
    > > > > > > > > > affected rows during INSERT. this table is visible to
    > trigger
    > > > body
    > > > > > > only.
    > > > > > > > > ie
    > > > > > > > > > if you are writing a trigger FOR INSERT operation, you can
    > > refer
    > > > > to
    > > > > > > this
    > > > > > > > > > table inside the trigger body.
    > > > > > > > > > See BOL for more information.
    > > > > > > > > >
    > > > > > > > > > --
    > > > > > > > > > -Vishal
    > > > > > > > > > "Newbie" <noideanospam.com> wrote in message
    > > > > > > > > > news:eJL1Se9PDHA.3016TK2MSFTNGP10.phx.gbl...
    > > > > > > > > > > Hi
    > > > > > > > > > >
    > > > > > > > > > > I have the following tables TranDetails (3rd Party) with
    4
    > > > > fields
    > > > > > > > making
    > > > > > > > > > up
    > > > > > > > > > > primary key
    > > > > > > > > > > Supplier char 7
    > > > > > > > > > > GRN char 7
    > > > > > > > > > > Journal decimal
    > > > > > > > > > > JournalEntry decimal
    > > > > > > > > > > detail1
    > > > > > > > > > > detail1
    > > > > > > > > > > detail3
    > > > > > > > > > >
    > > > > > > > > > > InvMove
    > > > > > > > > > > Journal (PK) decimal
    > > > > > > > > > > JournalEntry (PK) decimal
    > > > > > > > > > > Stockcode char
    > > > > > > > > > > Supplier char
    > > > > > > > > > > Date date
    > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > > > > > GRNDetails
    > > > > > > > > > > GRN
    > > > > > > > > > > Supplier
    > > > > > > > > > > Stockcode
    > > > > > > > > > > etc
    > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > > > > > when a record is inserted into the TranDetails table I
    > would
    > > > > like
    > > > > > to
    > > > > > > > > > insert
    > > > > > > > > > > a record into GRNDetails table based on a query from the
    > > > InvMove
    > > > > > and
    > > > > > > > > > > TransDetails table.
    > > > > > > > > > > I want the query to be limited to the record that has
    just
    > > > been
    > > > > > > > entered
    > > > > > > > > > into
    > > > > > > > > > > the TranDetails table i.e. the query would only ever
    > return
    > > > one
    > > > > > > record
    > > > > > > > > > >
    > > > > > > > > > > How would I do this using a trigger? I am a complete
    > > beginner
    > > > > > where
    > > > > > > > > this
    > > > > > > > > > is
    > > > > > > > > > > concerned so any help would be greatly appreciated
    > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > >
    > > > > > > > >
    > > > > > > >
    > > > > > > >
    > > > > > >
    > > > > > >
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Newbie Guest

  11. #11

    Default Re: trigger question

    Thanks but it still doesn't work :-(
    the problem I have now is that when a negative record is added to the
    GrnDetails table the trigger is adding two records to the GRN table because
    each negative has to be linked to a positive Grn.

    The primary key on the GrnDetails table are Grn, Journal, JournalEntry and
    GrnSource therefore the subquery which just asks for the Grn to be included
    is returning two records - one positive and one negative

    How can I limit the subquery to take account of the Journal, JournalEnrty,
    GrnSource and Grn?

    FYI here is the query so far . .

    insert into MIS.dbo.GRN(GRD.Supplier,
    GRD.Grn,
    GRD.Journal,
    GRD.JournalEntry,
    GRD.GrnSource,
    IST.Serial,
    GRD.PurchaseOrder,
    GRD.PurchaseOrderLin,
    GRD.StockCode,
    GRD.QtyReceived,
    GRD.QtyUom,
    GRD.DeliveryNote,
    GRD.OrigReceiptDate,
    INM.[Description],
    INM.LongDesc,
    INM.DrawOfficeNum,
    APA.SupAddr1,
    APA.SupAddr2,
    APA.SupAddr3,
    APM.SupplierClass,
    APA.SupAddr5,
    APA.SupAddr4,
    APA.SupPostalCode,
    IJD.Notation,
    IJD.RejectCode,
    APM.SupplierName)
    SELECT DISTINCT
    GRD.Supplier,
    GRD.Grn,
    GRD.Journal,
    GRD.JournalEntry,
    GRD.GrnSource,
    IST.Serial,
    GRD.PurchaseOrder,
    GRD.PurchaseOrderLin,
    GRD.StockCode,
    GRD.QtyReceived,
    GRD.QtyUom,
    GRD.DeliveryNote,
    GRD.OrigReceiptDate,
    INM.[Description],
    INM.LongDesc,
    INM.DrawOfficeNum,
    APA.SupAddr1,
    APA.SupAddr2,
    APA.SupAddr3,
    APM.SupplierClass,
    APA.SupAddr5,
    APA.SupAddr4,
    APA.SupPostalCode,
    IJD.Notation,
    IJD.RejectCode,
    APM.SupplierName

    FROM dbo.GrnDetails GRD INNER JOIN

    dbo.ApSupplier APM
    ON GRD.Supplier = APM.Supplier INNER JOIN

    dbo.ApSupplierAddr APA
    ON APM.Supplier = APA.Supplier LEFT OUTER JOIN

    dbo.InvJournalDet IJD
    ON GRD.Journal = IJD.Journal AND
    GRD.JournalEntry = IJD.EntryNumber AND
    GRD.GrnSource = IJD.GrnSource LEFT OUTER JOIN

    dbo.InvMaster INM
    ON GRD.StockCode = INM.StockCode LEFT OUTER JOIN

    dbo.InvSerialTrn IST
    ON GRD.Grn = IST.Reference
    Where GRD.Grn in
    (select distinct inserted.Grn from inserted INNER JOIN dbo.GrnDetails GRD2
    ON inserted.JournalEntry = GRD2.JournalEntry AND inserted.Journal =
    GRD2.Journal and inserted.Grn = GRD2.Grn and inserted.GrnSource =
    GRD2.GrnSource)




    "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    news:utBoJtIQDHA.2832TK2MSFTNGP10.phx.gbl...
    > Try:
    > insert into RejectTrigger(GRD.Supplier,
    > GRD.Grn,
    > GRD.PurchaseOrder,
    > GRD.PurchaseOrderLin,
    > GRD.StockCode,
    > GRD.QtyReceived,
    > GRD.QtyUom,
    > GRD.DeliveryNote,
    > GRD.OrigReceiptDate,
    > INM.LongDesc)
    > SELECT DISTINCT
    > GRD.Supplier,
    > GRD.Grn,
    > GRD.PurchaseOrder,
    > GRD.PurchaseOrderLin,
    > GRD.StockCode,
    > GRD.QtyReceived,
    > GRD.QtyUom,
    > GRD.DeliveryNote,
    > GRD.OrigReceiptDate,
    > INM.LongDesc
    >
    > FROM dbo.GrnDetails GRD INNER JOIN
    >
    > dbo.ApSupplier APM
    > ON GRD.Supplier = APM.Supplier INNER JOIN
    >
    > dbo.ApSupplierAddr APA
    > ON APM.Supplier = APA.Supplier LEFT OUTER JOIN
    >
    > dbo.InvJournalDet IJD
    > ON GRD.Journal = IJD.Journal AND
    > GRD.JournalEntry = IJD.EntryNumber LEFT OUTER JOIN
    >
    > dbo.InvMaster INM
    > ON GRD.StockCode = INM.StockCode LEFT OUTER JOIN
    >
    > dbo.InvSerialTrn IST
    > ON GRD.Grn = IST.Reference
    > Where GRD.Grn in
    > (select distinct inserted.Grn from inserted where inserted.qty < 0 ) --if
    > you want to have negative records / change the condition to > 0 to have
    > positive value records.
    >
    > --
    > -Vishal
    > "Newbie" <noideanospam.com> wrote in message
    > news:#$X1A2$PDHA.1720TK2MSFTNGP11.phx.gbl...
    > > Thanks for your help but I couldn't get this to work
    > >
    > > Here is what I have so far but this puts records in the table without
    any
    > > regard to qty - I want to be able to limit it to negative records
    > >
    > > What do I do ?
    > >
    > >
    > > CREATE TRIGGER receipts ON[dbo].[GrnDetails]
    > > FOR INSERT
    > > AS
    > > insert into RejectTrigger(GRD.Supplier,
    > > GRD.Grn,
    > > GRD.PurchaseOrder,
    > > GRD.PurchaseOrderLin,
    > > GRD.StockCode,
    > > GRD.QtyReceived,
    > > GRD.QtyUom,
    > > GRD.DeliveryNote,
    > > GRD.OrigReceiptDate,
    > > INM.LongDesc)
    > > SELECT DISTINCT
    > > GRD.Supplier,
    > > GRD.Grn,
    > > GRD.PurchaseOrder,
    > > GRD.PurchaseOrderLin,
    > > GRD.StockCode,
    > > GRD.QtyReceived,
    > > GRD.QtyUom,
    > > GRD.DeliveryNote,
    > > GRD.OrigReceiptDate,
    > > INM.LongDesc
    > >
    > >
    > > FROM dbo.GrnDetails GRD INNER JOIN
    > >
    > > dbo.ApSupplier APM
    > > ON GRD.Supplier = APM.Supplier INNER JOIN
    > >
    > > dbo.ApSupplierAddr APA
    > > ON APM.Supplier = APA.Supplier LEFT OUTER JOIN
    > >
    > > dbo.InvJournalDet IJD
    > > ON GRD.Journal = IJD.Journal AND
    > > GRD.JournalEntry = IJD.EntryNumber LEFT OUTER JOIN
    > >
    > > dbo.InvMaster INM
    > > ON GRD.StockCode = INM.StockCode LEFT OUTER JOIN
    > >
    > > dbo.InvSerialTrn IST
    > > ON GRD.Grn = IST.Reference
    > > Where GRD.Grn in
    > > (select distinct inserted.Grn from inserted )
    > >
    > >
    > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > news:e0b86m$PDHA.2052TK2MSFTNGP11.phx.gbl...
    > > > add where cluase to the query
    > > > ex:
    > > > SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    > > > dbo.TranDetails.OrigReceiptDate
    > > > FROM dbo.TranDetails INNER JOIN
    > > > dbo.InvMove ON dbo.TranDetails.StockCode =
    > > > dbo.InvMove.StockCode inner join inserted on
    > > > inserted.grn = dbo.trandetails.grn
    > > > where inserted.qty < 0--check this condition and use right column
    name.
    > > >
    > > > --
    > > > -Vishal
    > > > "Newbie" <noideanospam.com> wrote in message
    > > > news:eNG5Sd$PDHA.3144tk2msftngp13.phx.gbl...
    > > > > Thanks that seems to work :-) one last question . . .
    > > > >
    > > > > If I wanted to limit the trigger to only inserting records into my
    > table
    > > > if
    > > > > the qty < 0 for the record in the inserted table what would I need
    to
    > > > add?
    > > > >
    > > > > FYI I used the 1st option you gave using the subquery
    > > > >
    > > > > Thanks
    > > > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > > > news:efhJXe#PDHA.2052TK2MSFTNGP11.phx.gbl...
    > > > > > i assume you want to add INSERTED table as a join in the query.
    make
    > > > sure
    > > > > > you are using proper fieldnames in join clause.
    > > > > >
    > > > > > try:
    > > > > >
    > > > > > SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    > > > > > dbo.TranDetails.OrigReceiptDate
    > > > > > FROM dbo.TranDetailsINNER JOIN
    > > > > > dbo.InvMove ON dbo.TranDetails.StockCode =
    > > > > > dbo.InvMove.StockCode
    > > > > > WHERE dbo.TranDetails.Grn in
    > > > > > (select grn from inserted)
    > > > > >
    > > > > > OR
    > > > > >
    > > > > > SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    > > > > > dbo.TranDetails.OrigReceiptDate
    > > > > > FROM dbo.TranDetails INNER JOIN
    > > > > > dbo.InvMove ON dbo.TranDetails.StockCode =
    > > > > > dbo.InvMove.StockCode inner join inserted on
    > > > > > inserted.grn = dbo.trandetails.grn
    > > > > >
    > > > > >
    > > > > > --
    > > > > > -Vishal
    > > > > > "Newbie" <noideanospam.com> wrote in message
    > > > > > news:#4bx4Y#PDHA.2768tk2msftngp13.phx.gbl...
    > > > > > > no I want to insert into table RECEIPTS (this syntax wasn't
    > included
    > > > > > before)
    > > > > > > the results of the query
    > > > > > >
    > > > > > > SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    > > > > > > dbo.TranDetails.OrigReceiptDate
    > > > > > > FROM dbo.TranDetailsINNER JOIN
    > > > > > > dbo.InvMove ON dbo.TranDetails.StockCode =
    > > > > > > dbo.InvMove.StockCode
    > > > > > > WHERE . . . the Grn = the Grn of the record that has just
    been
    > > > > > inserted
    > > > > > > into the TranDetails table
    > > > > > >
    > > > > > >
    > > > > > >
    > > > > > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > > > > > news:OyNGYT#PDHA.3144tk2msftngp13.phx.gbl...
    > > > > > > > are you trying to insert the records into inmove table? if yes
    > > then
    > > > > the
    > > > > > > > syntax would be.
    > > > > > > >
    > > > > > > > insert into invmove(col1,col2,col2)
    > > > > > > > SELECT a.Supplier, a.Grn,
    > > > > > > > a.OrigReceiptDate
    > > > > > > > FROM dbo.TranDetails a INNER JOIN inserted b on
    > > > > > > > a.stockcode = b.stockcode
    > > > > > > > where .....
    > > > > > > >
    > > > > > > > --
    > > > > > > > -Vishal
    > > > > > > > "Newbie" <noideanospam.com> wrote in message
    > > > > > > > news:esX$pQ#PDHA.3880tk2msftngp13.phx.gbl...
    > > > > > > > > Thanks but I'm still confused . . . . . :-(
    > > > > > > > >
    > > > > > > > > where does the InvMove table come into the query?
    > > > > > > > > does the inserted temp table just replace the references to
    > the
    > > > > > > > TranDetails
    > > > > > > > > table?
    > > > > > > > >
    > > > > > > > > i.e
    > > > > > > > >
    > > > > > > > > SELECT a.Supplier, a.Grn,
    > > > > > > > > a.OrigReceiptDate
    > > > > > > > > FROM inserted a INNER JOIN
    > > > > > > > > dbo.InvMove b ON a.StockCode =
    > > > > > > > > b.StockCode
    > > > > > > > >
    > > > > > > > >
    > > > > > > > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > > > > > > > news:#g2e5K#PDHA.2244TK2MSFTNGP11.phx.gbl...
    > > > > > > > > > INSERTED table will have same table design as triggering
    > > table
    > > > ie
    > > > > > > > > > TranDetails (same columnname/datatype) hence you can
    > rewrite
    > > > your
    > > > > > > query
    > > > > > > > > as
    > > > > > > > > >
    > > > > > > > > > SELECT a.Supplier, a.Grn,
    > > > > > > > > > a.OrigReceiptDate
    > > > > > > > > > FROM dbo.TranDetails a INNER JOIN inserted b on
    > > > > > > > > > a.stockcode = b.stockcode
    > > > > > > > > > where .....
    > > > > > > > > >
    > > > > > > > > > --
    > > > > > > > > > -Vishal
    > > > > > > > > > "Newbie" <noideanospam.com> wrote in message
    > > > > > > > > > news:e2h7tE#PDHA.1748TK2MSFTNGP11.phx.gbl...
    > > > > > > > > > > Thanks for that but I am not sure understand what you
    > mean
    > > .
    > > > .
    > > > > .
    > > > > > > ..
    > > > > > > > > > >
    > > > > > > > > > > Would I need to do something like:
    > > > > > > > > > >
    > > > > > > > > > > CREATE TRIGGER newrecord
    > > > > > > > > > > ON TranDetails
    > > > > > > > > > > FOR INSERT
    > > > > > > > > > > AS
    > > > > > > > > > > SELECT dbo.TranDetails.Supplier, dbo.TranDetails.Grn,
    > > > > > > > > > > dbo.TranDetails.OrigReceiptDate
    > > > > > > > > > > FROM dbo.TranDetailsINNER JOIN
    > > > > > > > > > > dbo.InvMove ON dbo.GrnDetails.StockCode =
    > > > > > > > > > > dbo.InvMove.StockCode
    > > > > > > > > > > WHERE ???????
    > > > > > > > > > >
    > > > > > > > > > > where should I be referencing the INSERTED temp table?
    > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > > > > > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > > > > > > > > > > news:u59y8h9PDHA.2852tk2msftngp13.phx.gbl...
    > > > > > > > > > > > Make use of INSERTED temporary table,the inserted
    table
    > > > stores
    > > > > > > > copies
    > > > > > > > > of
    > > > > > > > > > > the
    > > > > > > > > > > > affected rows during INSERT. this table is visible to
    > > > trigger
    > > > > > body
    > > > > > > > > only.
    > > > > > > > > > > ie
    > > > > > > > > > > > if you are writing a trigger FOR INSERT operation, you
    > can
    > > > > refer
    > > > > > > to
    > > > > > > > > this
    > > > > > > > > > > > table inside the trigger body.
    > > > > > > > > > > > See BOL for more information.
    > > > > > > > > > > >
    > > > > > > > > > > > --
    > > > > > > > > > > > -Vishal
    > > > > > > > > > > > "Newbie" <noideanospam.com> wrote in message
    > > > > > > > > > > > news:eJL1Se9PDHA.3016TK2MSFTNGP10.phx.gbl...
    > > > > > > > > > > > > Hi
    > > > > > > > > > > > >
    > > > > > > > > > > > > I have the following tables TranDetails (3rd Party)
    > with
    > > 4
    > > > > > > fields
    > > > > > > > > > making
    > > > > > > > > > > > up
    > > > > > > > > > > > > primary key
    > > > > > > > > > > > > Supplier char 7
    > > > > > > > > > > > > GRN char 7
    > > > > > > > > > > > > Journal decimal
    > > > > > > > > > > > > JournalEntry decimal
    > > > > > > > > > > > > detail1
    > > > > > > > > > > > > detail1
    > > > > > > > > > > > > detail3
    > > > > > > > > > > > >
    > > > > > > > > > > > > InvMove
    > > > > > > > > > > > > Journal (PK) decimal
    > > > > > > > > > > > > JournalEntry (PK) decimal
    > > > > > > > > > > > > Stockcode char
    > > > > > > > > > > > > Supplier char
    > > > > > > > > > > > > Date date
    > > > > > > > > > > > >
    > > > > > > > > > > > >
    > > > > > > > > > > > > GRNDetails
    > > > > > > > > > > > > GRN
    > > > > > > > > > > > > Supplier
    > > > > > > > > > > > > Stockcode
    > > > > > > > > > > > > etc
    > > > > > > > > > > > >
    > > > > > > > > > > > >
    > > > > > > > > > > > > when a record is inserted into the TranDetails table
    I
    > > > would
    > > > > > > like
    > > > > > > > to
    > > > > > > > > > > > insert
    > > > > > > > > > > > > a record into GRNDetails table based on a query from
    > the
    > > > > > InvMove
    > > > > > > > and
    > > > > > > > > > > > > TransDetails table.
    > > > > > > > > > > > > I want the query to be limited to the record that
    has
    > > just
    > > > > > been
    > > > > > > > > > entered
    > > > > > > > > > > > into
    > > > > > > > > > > > > the TranDetails table i.e. the query would only ever
    > > > return
    > > > > > one
    > > > > > > > > record
    > > > > > > > > > > > >
    > > > > > > > > > > > > How would I do this using a trigger? I am a
    complete
    > > > > beginner
    > > > > > > > where
    > > > > > > > > > > this
    > > > > > > > > > > > is
    > > > > > > > > > > > > concerned so any help would be greatly appreciated
    > > > > > > > > > > > >
    > > > > > > > > > > > >
    > > > > > > > > > > >
    > > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > >
    > > > > > > > >
    > > > > > > >
    > > > > > > >
    > > > > > >
    > > > > > >
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Newbie Guest

  12. #12

    Default Re: trigger question

    can you post DDL/some sample records that affects the required condition
    (dont give me all the column names only relevant columns). and expected
    resultset to be entered into the required table. It seems your query is
    getting too longer and i think there are unncessary joins.

    -Vishal


    Vishal Parkar Guest

  13. #13

    Default Re: trigger question

    Record entered into GrnDetail table
    Positive receipt:

    (PK) Grn 12345
    (PK) JournalEntry 20
    (PK) Journal 2345
    (PK) GrnSource I
    QtyReceived 100

    Negative receipt
    Grn 12345
    JournalEntry 21
    Journal2346
    GrnSource I
    Qty Received -100

    The other columns go and get some info

    At the moment the GRN table is populated . . . . .

    1. With a positive receipt
    1 record with details as per positive receipt above

    2. with a negative receipt
    2 records - 1 with details as per positive reciept and 1 with details as
    per negative receipt

    This means that whenever a negative receipt is performed the GRN table will
    have a duplicate record for the positive receipt i.e one that is created at
    the time of positive transaction and one that is created at the time of
    negative transaction.

    Thanks for your continued help

    A
    "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    news:e2QYHtJQDHA.1624tk2msftngp13.phx.gbl...
    > can you post DDL/some sample records that affects the required condition
    > (dont give me all the column names only relevant columns). and expected
    > resultset to be entered into the required table. It seems your query is
    > getting too longer and i think there are unncessary joins.
    >
    > -Vishal
    >
    >

    Newbie Guest

  14. #14

    Default Trigger Question

    Hello again everyone, thanks for all your help
    previously. Have another trigger question...

    Can a trigger populate fields in another database or is it
    functional only in the database that it is created in? I
    tried looking in BOL but couldn't find anything on this.
    All I found was that it can update/insert fields in
    another table in the same db but nothing about other
    db's. Thanks in advance for your help.
    Harbir Singh Guest

  15. #15

    Default Re: Trigger Question

    Yes, you can. You will have to use 3-part (local) of 4-part (remote) naming to access the appropriate objects. Also check out the following for sample code:

    [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_refintegrity.asp[/url]

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    [url]www.pinnaclepublishing.com/sql[/url]


    "Harbir Singh" <Harbir77hotmail.com> wrote in message news:43ea01c340ae$499b2f60$a401280aphx.gbl...
    Hello again everyone, thanks for all your help
    previously. Have another trigger question...

    Can a trigger populate fields in another database or is it
    functional only in the database that it is created in? I
    tried looking in BOL but couldn't find anything on this.
    All I found was that it can update/insert fields in
    another table in the same db but nothing about other
    db's. Thanks in advance for your help.

    Tom Moreau Guest

  16. #16

    Default Re: trigger question

    use Inserted table for chacking presence/absence of your inserted row in
    Table B. Please post you code for specific problem..
    Thanks


    "Joel Russell" <com> wrote in message
    news:phx.gbl... 
    B. 
    Table 


    Nedu Guest

  17. #17

    Default Trigger Question

    For example i have the ff: trigger:

    CREATE TRIGGER MyTrigger
    ON MyTable
    FOR DELETE, INSERT, UPDATE
    AS
    BEGIN
    --Do Something
    END

    i just want to know if there's a way to determine
    what kind of DML (is it DELETE,INSERT,UPDATE?) triggered
    my trigger... thanks a lot!

    --
    gani





    gani Guest

  18. #18

    Default Trigger Question

    Greetings:

    Is there any way to pass dynamic data into a trigger function? I think the
    answer is no, but just in case...

    TIA
    --
    Work: 1-336-372-6812
    Cell: 1-336-363-4719
    email: [email]terryesc1.com[/email]

    ---------------------------(end of broadcast)---------------------------
    TIP 9: the planner will ignore your desire to choose an index scan if your
    joining column's datatypes do not match

    Terry Lee Tucker Guest

  19. #19

    Default Re: Trigger Question

    On Wed, Jan 05, 2005 at 02:56:27PM -0500, Terry Lee Tucker wrote:
    >
    > Is there any way to pass dynamic data into a trigger function? I think the
    > answer is no, but just in case...
    What do you mean by "dynamic data"? What problem are you trying
    to solve?

    --
    Michael Fuhr
    [url]http://www.fuhr.org/~mfuhr/[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 1: subscribe and unsubscribe commands go to [email]majordomopostgresql.org[/email]

    Michael Fuhr Guest

  20. #20

    Default Re: Trigger Question

    When I assign a value to a certain column in a table A, I want to be able to
    assign a value in table A to table B and then to assign a value in table B to
    table C. I was wanting to do this from the trigger level in an AFTER UPDATE
    trigger to ensure it gets done. I would like to be able to pass the serial
    keys of the associated records in table B and C to the AFTER UPDATE trigger
    so I could find the records in tables B and C and perform the updates.

    Does that make sense?

    Thanks for the reply...

    On Wednesday 05 January 2005 03:18 pm, Michael Fuhr saith:
    > On Wed, Jan 05, 2005 at 02:56:27PM -0500, Terry Lee Tucker wrote:
    > > Is there any way to pass dynamic data into a trigger function? I think
    > > the answer is no, but just in case...
    >
    > What do you mean by "dynamic data"? What problem are you trying
    > to solve?
    >
    > --
    > Michael Fuhr
    > [url]http://www.fuhr.org/~mfuhr/[/url]
    --
    Work: 1-336-372-6812
    Cell: 1-336-363-4719
    email: [email]terryesc1.com[/email]

    ---------------------------(end of broadcast)---------------------------
    TIP 8: explain yze is your friend

    Terry Lee Tucker Guest

Page 1 of 2 12 LastLast

Similar Threads

  1. SQL Server Trigger question
    By ego-adam in forum Coldfusion Database Access
    Replies: 3
    Last Post: October 14th, 01:46 AM
  2. An trigger question
    By ppl in forum Informix
    Replies: 1
    Last Post: November 12th, 05:30 PM
  3. question about trigger
    By Piotr in forum IBM DB2
    Replies: 7
    Last Post: September 17th, 01:49 PM
  4. Trigger question ...
    By Bob Castleman in forum Microsoft SQL / MS SQL Server
    Replies: 7
    Last Post: July 3rd, 04:22 PM
  5. Simple trigger question
    By Kurt in forum Oracle Server
    Replies: 3
    Last Post: January 14th, 02:07 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