মডিউল 12 · 3-এর পাঠ 4কোর্সে 47/57~15 min

মডিউল পাঠ (3/4)

ট্রিগার পরিচিতি

A trigger is a magic box (a sensor) that automatically runs a piece of logic BEFORE or AFTER an insert, update, or delete event in the data.\nBefore teaching a table to react, though, we need to give it a special "Trigger Function" written in PL/pgSQL that must end by returning a synthetic format called "TRIGGER".

These functions don't accept normal arguments in parentheses; instead they offer the magic built-in objects NEW and OLD for free, which contain the original or incoming row data from the table the sensor is watching!

SQL

CREATE OR REPLACE FUNCTION audit_deletions()
RETURNS TRIGGER AS $$
BEGIN
  -- OLD.id is magically the id value of the poor record being deleted
  INSERT INTO audit_log (table_name, record_id, action)
  VALUES (TG_TABLE_NAME, OLD.id, 'DELETE');

  RETURN OLD; -- Triggers before a Delete route must grant the exit
END;
$$ LANGUAGE plpgsql;

This procedure does the hard work of writing to the log. Later, in a second step, it will be permanently wired to the events of the table we want to protect.

Try it yourself

ব্যায়াম#sql.m12.l3.e1

প্রচেষ্টা: 0লোড হচ্ছে...

We need to protect customers: no one under 18 years old (our virtual check) or, beyond fictional logic, we want to force the 'first_name' string passed at INSERT time to always be stored in uppercase in the database, ignoring any JS client formatting.\nWrite a Trigger Function called 'force_uppercase_name()'. It accepts no () arguments.\nIt returns TRIGGER. Create the body template.\nForce-assign the whole incoming object via the magic assignment 'NEW.first_name = UPPER(NEW.first_name);'. Then RETURN the reshaped NEW variable.

সম্পাদক লোড হচ্ছে...

ইঙ্গিত দেখান

CREATE OR REPLACE FUNCTION force_uppercase_name() RETURNS TRIGGER AS $$ BEGIN NEW.first_name = UPPER(NEW.first_name); RETURN NEW; END; $$ LANGUAGE plpgsql;

সমাধান 3 প্রচেষ্টার পরে উপলব্ধ

ব্যায়াম#sql.m12.l3.e2