Creating a Trigger to Only Run When a New Table Is Being Created

Creating a trigger to only run when a new table is being created

CREATE OR REPLACE TRIGGER 
  create_table_trigger
  AFTER CREATE ON SCHEMA
BEGIN
  IF SYS.DICTIONARY_OBJ_TYPE = 'TABLE' THEN
      ....
END;

For a list of EVENT attributes, refer to this page

http://ist.marshall.edu/ist480adbp/plsql_triggers.html (link is down)

Wayback machine link to the contents of the dead link above:
https://web.archive.org/web/20110809071133/http://ist.marshall.edu/ist480adbp/plsql_triggers.html

As far as I know, dictionary_obj_type is one of
TABLE|SEQUENCE|PROCEDURE|INDEX|FUNCTION|TYPE|PACKAGE

And dictionary_obj_name is just the name of the table/sequence/proc/etc.

• dictionary_obj_type Returns the type of the dictionary object on which the DDL operation that fired the trigger occurred.
• dictionary_obj_name Returns the name of the dictionary object on which the DDL operation that fired the trigger occurred.

Trigger after create table

There's nothing in MySQL allowing the definition of a trigger to fire upon table creation, sorry to say.

SQL Server 2016 Create Trigger to start after New Table is finished updating

There is no way to do this with a trigger. If you need to know when a CRUD operation on a table is complete, you would need to execute a command after the CRUD operation in the same process that launches it.

SQL SERVER 2008 TRIGGER ON CREATE TABLE

Yes, it's called a DDL trigger. The documentation for CREATE TRIGGER has a sample for DROP_SYNONYM (a very questionable choice for an example) but you'll want the CREATE_TABLE event instead. A better starting point in understanding how they work is probably here:

http://msdn.microsoft.com/en-us/library/ms190989.aspx

If you have more specific details, e.g. what exactly do you want to pass to this function (I assume you mean procedure), or what does the procedure do, we can provide more useful and specific help.

Firing a MySQL trigger on existing table upon creation of trigger

The answer is simple: a BEFORE UPDATE trigger only triggers on UPDATE, so you need a second trigger BEFORE INSERT to cover both use cases: update and insert:

delimiter //

CREATE TRIGGER Table1insert BEFORE INSERT ON Table1 
FOR EACH ROW 
BEGIN
    IF NEW.Col2<100 THEN
        SET NEW.Col2=1;
    END IF;
END;//

delimiter ;

SQL Server - Create single Trigger that runs for ALL tables in the database

Generic table triggers don't exist in SQL so you'll need to loop through each of your tables (INFORMATION_SCHEMA.Tables) and create your triggers for each using dynamic SQL.
(Or come up with another simple process to create triggers for each table.)

Creating a Trigger that runs on two tables

Since you only need to perform the check when b_date is in December, it's more efficient to add this as a when condition at the top of the trigger. This also simplifies the trigger logic.

create or replace trigger borrow_check_trg
    before insert on borrow 
    for each row
    when (to_char(new.b_date,'MM') = '12')
declare
    l_loc_id copy.loc_id%type;
begin
    select c.loc_id into l_loc_id
    from   copy c
    where  c.copy_id = :new.copy_id;

    if l_loc_id = 'LC0001' then
       raise_application_error(-20669, 'Books cannot be borrowed from the London store during December'); 
    end if;
end;

---

Related Topics