Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2011
    Posts
    2

    Unanswered: Trigger is not executing in PostgreSQL8.3.7

    Hi,

    I have one trigger that should get triggered once there is update in one of my table. But the problem is that sometimes(around 5 out of 50) trigger is not being fired.
    I have checked db logs and application logs but not getting anything useful.
    Do anyone have any idea if there is some problem with 8.3.7 version of postgre related to trigger execution?
    Or someone can give me some king of hine that I might miss and i should have an eye on that as well?

    Thanks,
    Jay

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Please show us your trigger code and the statement that you run where you expect the trigger to fire

  3. #3
    Join Date
    Apr 2011
    Posts
    2
    Thanks for your response.
    Here is my Trigger Script:

    CREATE OR REPLACE FUNCTION sendStatus()
    RETURNS trigger AS $$

    # Host and Port of the agent listener to be set before creating this function
    set myHost "<HostName>"
    set myPort "<Port>"

    set Id $NEW($1)
    set Status $NEW($2)

    puts "Trigger script(sendStatus) called with ID=$Id, Status=$Status"

    proc createSocket {host port} {
    if { [catch {set GD(sockId) [socket $myHost $myPort]}] } {
    puts "Error connecting to $myHost:$myPort"
    } else {
    puts "Socket created successfully..."
    if {[catch { fileevent $GD(sockId) writable [list svcHandler $GD(sockId)] }]} {
    puts "Error creating Event Handler on Socket=($GD(sockId))"
    } else {
    fconfigure $GD(sockId) -buffering none
    }
    }
    }

    proc svcHandler {sock} {
    }

    if {![info exists GD(sockId)]} {

    puts "Info does not exists, hence creating new socket..."

    if { [catch {set GD(sockId) [socket $myHost $myPort]}] } {
    puts "Error connecting to $myHost:$myPort"
    } else {
    puts "Socket created successfully..."
    if {[catch { fileevent $GD(sockId) writable [list svcHandler $GD(sockId)] }]} {
    puts "Error creating Event Handler on Socket=($GD(sockId))"
    } else {
    fconfigure $GD(sockId) -buffering none
    }
    }

    }

    if {[info exists GD(sockId)]} {
    puts "Existing Sock=($GD(sockId))"
    set statusStr "ID=$Id,STATUS=$Status\n"
    set sStr ""

    # send empty string to test agent connectivity
    if {[catch {puts $GD(sockId) $sStr}] } {
    puts "Cannot send to agent with Socket Id = ($GD(sockId))"
    unset GD(sockId)
    puts "Creating new socket..."

    if { [catch {set GD(sockId) [socket $myHost $myPort]}] } {
    puts "Error connecting to $myHost:$myPort"
    } else {
    puts "Socket created successfully..."
    if {[catch { fileevent $GD(sockId) writable [list svcHandler $GD(sockId)] }]} {
    puts "Error creating Event Handler on Socket=($GD(sockId))"
    } else {
    fconfigure $GD(sockId) -buffering none
    }
    }

    }

    # send actual request

    if {[catch {puts $GD(sockId) "$statusStr"}] } {
    puts "Error sending data($statusStr) to the agent with Socket Id = ($GD(sockId))"
    unset GD(sockId)
    puts "Creating new socket..."

    if { [catch {set GD(sockId) [socket $myHost $myPort]}] } {
    puts "Error connecting to $myHost:$myPort"
    } else {
    puts "Socket created successfully..."
    if {[catch { fileevent $GD(sockId) writable [list svcHandler $GD(sockId)] }]} {
    puts "Error creating Event Handler on Socket=($GD(sockId))"
    } else {
    fconfigure $GD(sockId) -buffering none
    if {[catch {puts $GD(sockId) "$statusStr"}] } {
    puts "Error sending data($statusStr) to the agent with Socket Id = ($GD(sockId))"
    unset GD(sockId)
    }
    }
    }

    } else {
    puts "Sent response : $statusStr"
    }
    }
    return
    $$ LANGUAGE 'pltclu';



    and here is my table on which trigger should execute:

    \d nsn_work_order;
    Table "public.nsn_work_order"
    Column | Type | Modifiers
    ------------------+-----------------------+-----------
    id | integer | not null
    contract | character varying(32) |
    todo | character varying(16) |
    work_date | date |
    status | character varying(32) |
    asb | character varying(32) |
    dslam | character varying(32) |
    dsl_norm | character varying(32) |
    xldv20equloc | character varying(32) |
    profile | character varying(32) |
    spectrum_profile | character varying(32) |
    processed | boolean |
    portid | character varying(20) |
    Indexes:
    "nsn_work_order_pkey" PRIMARY KEY, btree (id)
    Triggers:
    getstatus AFTER UPDATE ON nsn_work_order FOR EACH ROW EXECUTE PROCEDURE sendstatus('id', 'status')

    would you please suggest some clue?

    regsrds,
    jay

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    For future posts, please format your code using [code] tags to make it readable
    (For details see: http://www.dbforums.com/misc.php?do=bbcode )

    I have no idea what that trigger is doing, as I don't know that language, sorry.

    Do you at least see the log message somwhere that it is being called?

Posting Permissions

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