Results 1 to 10 of 10
  1. #1
    Join Date
    May 2011
    Posts
    5

    Unanswered: enterprisedb postgres studio

    I have managed to convert most of my schema from mysql to postgres but it doesn't seem to convert the functions or triggers.

    i have used the mysql editor in the program but it doesn't seem to show any errors and looks like it executed fine but no triggers or functions seem to appear.

    can anyone help.

    Code:
    create or replace function prodotti_price_log_after_insert_trigger()
    returns trigger
    language plpgsql volatile as $$
      declare previous_price real;
    begin
    
    
      SELECT prezzobuy INTO previous_price FROM prodotti WHERE codart=NEW.codart;
    
      IF previous_price > NEW.prezzobuy THEN
        UPDATE prodotti_descriptions SET social_networks_reduced=0 WHERE codart=NEW.codart;
      END IF;
    
    
    end
    $$;

    Code:
    create or replace function shopto_customers_before_update_trigger()
    returns trigger
    language plpgsql volatile as $$
      declare exists_already integer;
    
    
    
    
    begin
    
    
      IF NEW.platforms <> OLD.platforms THEN
        SELECT count(*) INTO exists_already FROM shopto_newsletters WHERE customer_id=OLD.id;
    
        IF exists_already > 0 THEN
          UPDATE shopto_newsletters SET platforms=NEW.platforms WHERE customer_id=NEW.id;
        ELSE
          INSERT INTO shopto_newsletters (created, modified, email, platforms, customer_id) VALUES (now(), now(), NEW.email, NEW.platforms, NEW.id);
        END IF;
      END IF;
      
    
    end
    $$;


    Code:
    create or replace function mtdb_magic_modified()
    returns trigger
    language plpgsql stable as $$
    begin
        NEW.modified = CURRENT_TIMESTAMP;
        return new;
    end;
    $$;

    those are just a few
    Last edited by shorif2000; 05-09-11 at 07:33.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Did you run the corresponding CREATE TRIGGER statements?

  3. #3
    Join Date
    May 2011
    Posts
    5
    Quote Originally Posted by shammat View Post
    Did you run the corresponding CREATE TRIGGER statements?
    Yes i have, same thing. it runs the query OK but nothing appears under the sidebar under triggers


    Code:
    create trigger "mtdb_after_update" after update ON shopto.prodotti
      for each row execute procedure prodotti_after_update_trigger();

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I have no idea what that "sidebar" thing is.

    Is anything visible in the system catalog pg_trigger?

  5. #5
    Join Date
    May 2011
    Posts
    5
    Quote Originally Posted by shammat View Post
    I have no idea what that "sidebar" thing is.

    Is anything visible in the system catalog pg_trigger?


    In the object browser in pgAdmin III i do not see any function or triggers.

    How ever if i take off the ''OR REPLACE'' on the trigger and run the create trigger statement they seem to exist as it says it exists already but the functions do not?

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Is anything visible in the system catalog pg_trigger?

  7. #7
    Join Date
    May 2011
    Posts
    5
    Quote Originally Posted by shammat View Post
    Is anything visible in the system catalog pg_trigger?
    Under my database, under catalogs section I have 2 default ones i believe
    - ANSI
    - PostgreSQL (pg_catalog)

    There are many triggers and functions under pg_catalog but none which our mines

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by shorif2000 View Post
    There are many triggers and functions under pg_catalog but none which our mines
    Did you commit all your CREATE statements?

  9. #9
    Join Date
    May 2011
    Posts
    5
    Quote Originally Posted by shammat View Post
    Did you commit all your CREATE statements?
    yes. .....

  10. #10
    Join Date
    May 2008
    Posts
    277
    The code snippets you are posting cannot be what you're actually creating in the DBMS, because they have several errors:

    1. The final END requires a semicolon ( ; ) after it.

    2. All your trigger functions must return a value, which none of yours do. Per-statement triggers must return NULL. After per-row triggers must return NULL. Before per-row triggers must either return NULL to cancel the operation, or the row to insert/update/delete.

    Both of these things should be generating errors while either loading the SQL or attempting to the run the trigger.

Posting Permissions

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