求助：基于Yii2的PostgreSQL项目，Cpanel中phpPgAdmin无法创建触发器函数

Hey there! Since you're new to PostgreSQL and struggling to find the trigger function option in phpPgAdmin on cPanel, let me walk you through three reliable ways to get those triggers set up on your production environment:

Sometimes phpPgAdmin's interface might hide options due to version limitations or restricted permissions, so running raw SQL is the most foolproof approach. Just reuse the trigger function and trigger code you tested locally:

• Log into phpPgAdmin via cPanel, then navigate to your target database
• Click the SQL tab in the top navigation bar
• Paste your tested SQL code (example below) and hit "Execute":

  -- First create the trigger function
  CREATE OR REPLACE FUNCTION update_timestamp()
  RETURNS TRIGGER AS $$
  BEGIN
      -- Replace this with your actual trigger logic
      NEW.updated_at = NOW();
      RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  
  -- Then bind the function to a table with a trigger
  CREATE TRIGGER trigger_update_timestamp
  BEFORE INSERT OR UPDATE ON your_target_table
  FOR EACH ROW EXECUTE FUNCTION update_timestamp();
  

• If no errors pop up, your trigger is successfully created!

If you prefer using the UI, double-check these steps—you might have missed the right section:

• Navigate to your target database, then click the name of the table you want to add a trigger to
• Look for the Triggers tab in the table's detail page (it might be tucked next to other tabs like "Structure" or "Data")
• If you don't see the Triggers tab, first create your trigger function under the Functions tab:
  • Go back to the database homepage, click "Functions"
  • Click "Create Function", set the return type to TRIGGER, language to plpgsql, then paste your function body
• Once the function exists, return to the table's Triggers tab and click "Create Trigger" to bind it to the table

Since your project uses Yii2, migrations are a great way to keep local and production database structures in sync:

• Create a new migration file locally with:

  yii migrate/create add_table_trigger
  

• Open the generated migration file, and add your trigger creation logic to the up() method (and rollback logic to down()):

  public function up()
  {
      // Create the trigger function
      $this->execute("
          CREATE OR REPLACE FUNCTION update_timestamp()
          RETURNS TRIGGER AS $$
          BEGIN
              NEW.updated_at = NOW();
              RETURN NEW;
          END;
          $$ LANGUAGE plpgsql;
      ");
  
      // Create the trigger
      $this->execute("
          CREATE TRIGGER trigger_update_timestamp
          BEFORE INSERT OR UPDATE ON your_target_table
          FOR EACH ROW EXECUTE FUNCTION update_timestamp();
      ");
  }
  
  public function down()
  {
      // Rollback: delete trigger first, then function
      $this->execute("DROP TRIGGER IF EXISTS trigger_update_timestamp ON your_target_table;");
      $this->execute("DROP FUNCTION IF EXISTS update_timestamp();");
      return true;
  }
  

• Push the migration file to your production server, then run yii migrate in the terminal to execute the changes automatically.

内容的提问来源于stack exchange，提问作者Np Kanimozhi