logo
down
shadow

Exclude updated record from trigger function


Exclude updated record from trigger function

Content Index :

Exclude updated record from trigger function
Tag : sql , By : Ben Humphrys
Date : January 11 2021, 03:34 PM

I think the issue was by ths following , I figured it out..
Just had to change the comparison to exclude updating the value of OLD.priority, return NEW, and also change the trigger to run BEFORE and not AFTER
BEGIN
    CASE TG_OP
        WHEN 'UPDATE' THEN
            UPDATE
                link
            SET
                priority = priority + 1
            WHERE
                link.priority >= NEW.priority AND
                NOT link.priority >= OLD.priority;
    END CASE;

    RETURN NEW;
END;

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Detecting the record that was updated within a Trigger


Tag : sql-server , By : UnKnownUser
Date : March 29 2020, 07:55 AM
Hope this helps There is no "updated table" in update trigger. The values before update statement are in "deleted" table and new values are in "inserted" table. You can check whether a specific column was updated or not by using COLUMNS_UPDATED function.
Here is a simple example: An Introduction to Triggers -- Part II

Show which record was updated by using trigger


Tag : database , By : user113409
Date : March 29 2020, 07:55 AM
hope this fix your issue I am a freshmen in PL/SQL and a have such a problem: , You don't need a table reference in the trigger. Just use :new:
Create or replace trigger inform
after update of scontact_number on SUPLIER
for each row
DECLARE 
    x SUPLIER.ssurname%TYPE;
BEGIN
    Select :new.SSURNAME
    into x
    from dual;

    dbms_output.put_line('New tel number for ' || x);
END;
/ 
BEGIN
    dbms_output.put_line('New tel number for ' || :new.SSURNAME);
END;

Oracle get id of updated record in trigger update


Tag : oracle , By : Zinovate
Date : March 29 2020, 07:55 AM
I hope this helps . Here's an example based on Scott's schema. I altered the DEPT table, adding a new column (SUM_SAL) which is supposed to contain sum of all salaries in that department.
First, the good, old mutating table way.
SQL> create or replace trigger trg_sumsal
  2    after update on emp
  3    for each row
  4  declare
  5    l_sum number;
  6  begin
  7    select sum(sal) into l_sum
  8      from emp
  9      where empno = :new.empno;
 10
 11    update dept set sum_sal = l_sum
 12      where deptno = :new.deptno;
 13  end;
 14  /

Trigger created.

SQL> update emp set sal = 5000 where ename = 'KING';
update emp set sal = 5000 where ename = 'KING'
       *
ERROR at line 1:
ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.TRG_SUMSAL", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRG_SUMSAL'


SQL>
SQL> create or replace trigger trg_sumsal
  2    for update or insert on emp
  3    compound trigger
  4
  5    l_deptno emp.deptno%type;
  6
  7    after each row is
  8    begin
  9      l_deptno := :new.deptno;
 10    end after each row;
 11
 12    after statement is
 13      l_sum number;
 14    begin
 15      select sum(sal) into l_Sum
 16        from emp
 17        where deptno = l_deptno;
 18
 19      update dept set sum_sal = l_sum
 20      where deptno = l_deptno;
 21    end after statement;
 22  end;
 23  /

Trigger created.

SQL> update emp set sal = 10000 where ename = 'KING';

1 row updated.

SQL> select * From dept;

    DEPTNO DNAME          LOC              SUM_SAL
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK           13750
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>
SQL> create or replace trigger trg_sumsal
  2    for update or insert on emp
  3    compound trigger
  4
  5    type t_tab is table of number;
  6    l_tab t_tab := t_tab();
  7
  8    after each row is
  9    begin
 10      l_tab.extend;
 11      l_tab(l_tab.last) := :new.deptno;
 12    end after each row;
 13
 14    after statement is
 15      l_sum number;
 16    begin
 17      for i in l_tab.first .. l_tab.last loop
 18        select sum(sal) into l_Sum
 19          from emp
 20          where deptno = l_tab(i);
 21
 22        update dept set sum_sal = l_sum
 23          where deptno = l_tab(i);
 24      end loop;
 25    end after statement;
 26  end;
 27  /

Trigger created.
SQL> select * from dept;

    DEPTNO DNAME          LOC              SUM_SAL
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> update emp set sal = 10000 where ename in ('SMITH', 'KING');

2 rows updated.

SQL> select * from dept;

    DEPTNO DNAME          LOC              SUM_SAL
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK           13750
        20 RESEARCH       DALLAS             15975
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

Check if the record updated is the last record of the table in a trigger


Tag : sql , By : Kirks
Date : March 29 2020, 07:55 AM
To fix the issue you can do The answer to my question is NO. I have to do a COMPOUND TRIGGER to be able to do a select in the table that is mutating.
my trigger looks something like this :
CREATE OR REPLACE TRIGGER relation_person_event_AUR_TR
   FOR UPDATE
   ON relation_person_event
COMPOUND TRIGGER

  date_to_update relation_person_event.date%TYPE;
  idPerson       relation_person_event.id_Person%TYPE;
  idevent        relation_person_event.id_event%TYPE;
  NB_BIGGER      NUMBER;

  BEFORE EACH ROW IS
  BEGIN
    -- I can't access the :NEW in the AFTER STATEMENT, 
    -- so I need to save up the variables that I might use in the update.
    date_to_update := :NEW.DATE;
    idPerson := :NEW.id_Person;
    idevent := :NEW.id_event;
  END BEFORE EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    SELECT COUNT(*) INTO NB_BIGGER
      FROM relation_person_event T
     WHERE T.DATE > date_to_update;

    -- If 0, means that im updating the "last" row.
    IF NB_BIGGER = 0 THEN 
      UPDATE old_table O
         SET O.date = date_to_update,
             O.id_event = idevent
       WHERE O.id_Person = idPerson;
    END IF;
  END AFTER STATEMENT;
END relation_person_event_AUR_TR;

Update the record which is being inserted/updated using a trigger


Tag : oracle , By : artifex
Date : March 29 2020, 07:55 AM
Related Posts Related QUESTIONS :
  • Release item in current year
  • Conditional function not working as intended postgresql
  • SQL trigger, results not Grouped by
  • getting more records than expected with simple query in postgresql
  • get first occurrence of a certain combination of columns
  • Converting string timestamp to local-time zone in Oracle
  • Redshift - Filter out rows that are blank to left or right of a character
  • Teradata - Know the load volume by times of a table
  • How do I replace duplicate data with random data of same length in SQL
  • How to group by using pivot table
  • How can I achieve a Join for merging two tables FULL OUTER JOIN and avoid duplicates?
  • Multi Keyword search on multi-column sql
  • Computing the median of salaries under each manager in BigQuery SQL
  • How can insert value by selecting from another
  • Retrieving only the latest item for a set of data
  • update column with concatenating for that group only
  • SQL `SELECT 'some string value' AS fieldname` equivalent in Powershell?
  • How to determine the AVERAGE number of associated records
  • In Django, how can I build a set of criteria from a list?
  • Using replace() inside a loop
  • select user on create view
  • Calculate and aggregate column depending on a value in another colum (SQL)
  • Trigger to track all the insert or update on Oracle Table
  • Update column - remove first and last characters
  • T-SQL - Adding FK to table, make it do drop/null/efault automagically?
  • Get constraints and indexes on materialized views
  • SQL perform AVG after MAX
  • How do you join two tables A and B in order to get a non join column in A with data not in B?
  • Move Non-NULL Values Up Within Partition
  • Selecting rows based on two many-to-many relations
  • I have a table on which i want to do some advance calculation and get the table in a new shape
  • How to convert decimal to time in Oracle SQL?
  • Combine numbers to 1 row
  • Error: column does not exist when in fact it does? why?
  • How to fix: "Operation must use an updateable query" on update statement in Access VBA
  • Does information passes to a new table via CREATE TABLE update itself?
  • How to write a SQL statement which filters between two dates/times, with the date and time in separate columns
  • MS-Access-2016 Query Language 'ORDER BY' Clause
  • How to get totals for each age category?
  • get the first n characters of getdate()
  • Query to handle the dates
  • SQL grouping/counting on a string split function
  • ORDER BY and TOP statements in SQL works different on listing down the records and why?
  • SQL Server Computed Column: how to set it to NOT NULL, without persistence
  • Is nested select statement better than where statement?
  • Postgresql sequential scan slow performance on 500 million rows
  • Aggregate first order data to all orders - Microsoft SQL Server
  • Sorting comma delimited datasets in row
  • Question about the correct data structure
  • Showing two temp tables data in a single table
  • How can I find out the query execution time in Oracle SQL using Toad?
  • convert quarter number to month name in sql
  • Find max value from column that has a json object with key-value pairs
  • case statement based on 3 columns in 1 table
  • How to write a SQL query to select batsman wiht highest score than the previous year
  • Find Substring in SQL
  • Using VALUE to make temporary table
  • What is the correct way of writing an exclusive left-join on two tables?
  • Is it possible to run a make table query using a recordset as the source in MS Access (VBA)?
  • INSERT INTO select distinct null PostgreSQL
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com