logo
down
shadow

Can I directly define a trigger in all_triggers table on a table?


Can I directly define a trigger in all_triggers table on a table?

Content Index :

Can I directly define a trigger in all_triggers table on a table?
Tag : oracle , By : Bin
Date : December 01 2020, 05:00 PM

hop of those help? Copying the triggers from one table to another can be done by copying DDL, and not updating all_triggers table. This can be done by using DBMS_METADATA.
The closest practical example I found here: Copy Triggers when you Copy a Table
declare
  p_src_tbl varchar2(30):= 'PERSONS';   --your table name
  p_trg_tbl varchar2(30):= 'PSN2';      --your trigger name
  l_ddl varchar2(32000);
begin
  execute immediate 'create table '||p_trg_tbl||' as select * from '||p_src_tbl||' where 1=2';
  for trg in (select trigger_name from user_triggers where table_name = p_src_tbl) loop
     l_ddl:= cast(replace(replace(dbms_metadata.get_ddl( 'TRIGGER', trg.trigger_name),p_src_tbl,p_trg_tbl),trg.trigger_name,substr(p_trg_tbl||trg.trigger_name, 1, 30)) as varchar2);
    execute immediate substr(l_ddl, 1, instr(l_ddl,'ALTER TRIGGER')-1);
  end loop;
end;
/

Comments
No Comments Right Now !

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

Share : facebook icon twitter icon

Can I select a set of rows from a table and directly insert that into a table or the same table in SQL?


Tag : sql , By : Ir0nh1de
Date : March 29 2020, 07:55 AM
To fix this issue You can do something like that, but you cannot Select * if you want to change a column value:
Insert into employee ( employeeId, someColumn, someOtherColumn )
  Select 2, someColumn, someOtherColumn
  From employee
  Where employeeId=1

Oracle APEX Trigger - Can I affect 'Table A' when I execute a trigger on the insert of 'Table B'?


Tag : oracle , By : drbillll
Date : March 29 2020, 07:55 AM
it should still fix some issue You are confusing Apex and database concepts: triggers are part of the database, not of Apex.
The syntax for the trigger would be:
CREATE OR REPLACE TRIGGER  "DEFAULT_LOG_ENTRY" AFTER insert on "MAIN_APEX"
for each row 
 begin
  insert into apex_logs (log_entry, log_date, circulation,
                         main_pk_ref, techwriter)
  values ('This log page was established. Actions and communcations are captured from this date and time onward.'
         , sysdate
         , 'External'
         , :new.main_pk
         , :new.TECHWRITER);
 end;

Is it good to create some intermediate table or directly use WITH query. Given that the table is a big table


Tag : sql , By : Frank
Date : March 29 2020, 07:55 AM
this one helps. You should try it.
Oracle does not (necessarily) materialize CTEs. That means that the entire query gets optimized, which no unnecessary reads and writes. In many cases, this results in a more efficient query plan.

can not insert row in a table while data redirecting from parent table to child table in trigger(stored procedure)


Tag : postgresql , By : user87225
Date : March 29 2020, 07:55 AM
To fix the issue you can do I am using postgres version: PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit , My problem is solved in this way...
CREATE OR REPLACE FUNCTION insert_tsttbl1_hourbase() RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'Hi...%', NEW;
    Execute 'set search_path to ' || TG_TABLE_SCHEMA|| '';
    INSERT INTO tsttbl1_hour0 values (NEW.*);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

mysql select from table B where condition is not met on table B directly, but through join on table A


Tag : mysql , By : Giles
Date : March 29 2020, 07:55 AM
it helps some times Given two tables A and B, which look like: , You could use:
SELECT *
FROM Table_b
WHERE A_id IN (SELECT A_Id
               FROM Table_b
               WHERE LENGTH(Value) = 4);
╔══════╦═══════╗
║ A_id ║ Value ║
╠══════╬═══════╣
║    3 ║ Goat  ║
║    3 ║ Cow   ║
╚══════╩═══════╝
Related Posts Related QUESTIONS :
  • Convert Coldfusion script in Oracle view with CASE and iteration
  • How to calculate the amount of ID monthly with the condition?
  • Ho to get UDT members by SQL?
  • How to set Oracle connection string for dotNET core console app?
  • Getting MySQL error stack trace on Oracle JDBC Connection
  • Why a long running pl/sql code that shows ACTIVE in v$session doesn't show up in v$active_session_history
  • Oracle SQL changes in sqldeveloper are not reflected in EFCore
  • Oracle query to get counts from multiple tables and display them side by side
  • Want to change NLS_DATE_FORMAT in oracle DB for a particular Schema only
  • How to convert char to number with mask
  • Oracle 11g Database NUMBER value with a zero at end, a bug?
  • How to convert this code from oracle to redshift?
  • How does the Oracle (+) join work in this scenario
  • External tables and directory objects
  • Edit RAW column in Oracle SQL Developer
  • Multiple values for the same URI parameter
  • Need Help To Resolve ORA-12154
  • How to unhide page item in oracle apex
  • How can I get this syntax to work in Oracle?
  • oralce apex_json to extract fields
  • get all records without fulfilling a condition
  • Last 5 months records dynamically in Oracle without sysdate
  • Serializing SDO_GEOMETRY type to text really slow
  • What's wrong with this UPDATE SQL JOIN syntax?
  • Oracle command works in database but not in SQLPlus
  • Checking collection data in where clause oracle
  • how to define multiple (WITH AS FUNCTIONS) in a single query?
  • Could I use plsql variable constant or variable with Flyway
  • Query joined partitioned tables
  • Compare time in a non-date format in Oracle
  • runtime plsql throwing table not exist error
  • In oracle 19c ,what is the maximum number of columns allowed in a table?
  • PLSQL: How to create a cursor using a variable as table_name
  • Update a row after Select Into variable
  • What does '%TYPE' mean following a parameter in procedure?
  • What do I have to do to enable the DML replication in the Oracle 18c EE?
  • Oracle: Convert rows into columns for output of joined table queries
  • I am able to connect to Oracle Db using Toad but connection using sqlplus is not working. Getting TNS timeout error
  • New line in TEXT #ORACLE
  • Comparing Dates in Case Statement
  • Passing string parameter JPA native query
  • OracleForms - form_success becomes true in key_commit despite there is database error
  • Why is TIMESTAMP DEFAULT CURRENT_TIMESTAMP not set?
  • Rank the managers based on how many employees they have
  • Extract when last character of a word is number Oracle
  • Flyway: create view as WITH (common table expression CTE)
  • Alternative to VALUES LESS THAN in interval partitioing creation query
  • Cannot connect to Oracle 19 with unixODBC
  • Oracle SQL Procedure. What is the error here?
  • Decreasing running total in oracle sql
  • Oracle subquery does not see the variable on second level
  • PL/SQL create trigger
  • Access out parameters of a procedure from another procedure in oracle
  • Oracle - Cannot fetch using table function on type-table, ORA-06504
  • How to mention schema name in dbms_metadata.get_ddl
  • Installing Worklight Server v506 with Oracle and Liberty does not create the expected directory structure
  • Stored Procedure using Execute Immediate with binding
  • Multiple columns in a single WHEN clause while using CASE in oracle
  • Table is index/full scanned based on the table it is joined to
  • SQL query initial and final balance as well sum of events
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com