Create A Trigger That Adds Corresponding Records Into MessageRecipient

Dec 10, 2012

Relevant tables:

create table messages
(
msgID number(10),
fromID number(10),
message varchar2(1024),
ToUserID number(10) default NULL,
ToGroupID number(10) default NULL,
dateSent date,
constraint messages_pk primary key(msgID),
constraint messages_fk1 foreign key(fromID) references profile(userID) on delete cascade,
constraint messages_fk2 foreign key(ToUserID) references profile(userID) on delete cascade
);
[code]....

I need to create a trigger that adds corresponding records into messageRecipient when a message is sent to a group. So, for example, the sql statement

insert into messages values(1, 2, 'message sent to group 3 from user 2', NULL, 3, currentdate);

should add records of the form (1, userID) into messageRecipient for every (3, userID) pair in groupMembership. Here are two triggers I've tried, neither of which works.

create or replace trigger SendGroupMessage
after insert on messages
FOR EACH ROW
when (new.ToUserID is NULL)
BEGIN
insert into messageRecipient(msgID,UserID)
[code]....

View 6 Replies


ADVERTISEMENT

PL/SQL :: Action Of Trigger To Create Records In Table

May 14, 2013

Using oracle 11.2.0.3 and wish to do the following.

Have trigger on table a on database a.

When a record is inserted into table a a record should be written first to table1 on database b , committed then a record written to table2 on database b as table2 has fk dependency on table 1.

How can we best achieve this.

Getting problems with the commit inside the trigger have tried pragma autonous transaction as well.

Get ora-02064 distributed transaction not supported when use the following code

create or replace trigger por_TRG_POP_IKNOW_MULTIPLE2

after INSERT OR UPDATE or delete   on por_MULTIPLE

for each row

-- tO ENSURE I-KNOW KEPT UP-TO-DATE WITH HOMIS MULTIPLE
-- delib not delet form i-know as if has been used by customer cannot delete
-- due to referential inetgrity constraints in i-know
-- Note parent_num updated on IKW via support.maintain_latest_dim_version.update_multiple until
-- When IKW becomes master of multiple info, this trigger can be removed
-- and support.maintain_latest_dim_version.update_multiple removed

declare
PRAGMA AUTONOMOUS_TRANSACTION;
v_dml_type varchar2(1);
begin
IF INSERTING OR UPDATING

[Code]....

--when matched THEN update set
--      a.MGRP_NUM b.mult_link_multiple_num,
--      MGRP_DESCRIPTION = (select mult_name from por_multiple
  --     where mult_code = mult_link_multiple_code

when not matched

then insert
(
a.MGRP_NUM,
a.MGRP_DESCRIPTION

)

[Code]...

View 7 Replies View Related

PL/SQL :: Create Trigger If Any Insertion In TAB1 Then Records Get Inserted Into TAB2?

Oct 30, 2012

i have requirement like this i don't know abt trigger

create trigger with the below:
Tables: TAB1 TAB2
Create a trigger, if any insertion in TAB1 then records should get inserted into TAB2
create a trigger, if any updation in TAB1 then record should get inserted into TAB2
Create a trigger, if any deletion in TAB1 then record should get inserted into TAB2

TAB1 and TAB2 can be any table.

View 2 Replies View Related

SQL & PL/SQL :: Trigger Not Inserting The Records?

Jun 21, 2012

I have written a trigger which insert and update on same table and the select statement for update and insert is same. My trigger update the record but doesn't insert data and doesn't throw error as well.

if i substitue all the where condidion value of insert statement then it return row and insert data but doesn't insert when trigger fire so there is no issue with the syntex.

create or replace
TRIGGER SRVCCLLS_RVW
AFTER INSERT OR UPDATE OR DELETE ON SD_SERVICECALLS
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
DECLARE

[Code]....

if i substitue all the where condidion value of insert statement then it return row and insert data but doesn't insert when trigger fire so there is no issue with the syntex

View 2 Replies View Related

SQL & PL/SQL :: Trigger To Move Records To History?

Mar 23, 2010

I have a table CISCOWORKS that contains dumps from Ciscoworks. Now I want to make a history table CISCOWORKS_HISTORY that contains all information I no longer need actively (entries that have the same CISCOWORKS_MAC as newer entries).

The two tables are like this, exactly the same.

DROP TABLE CISCOWORKS IF EXISTS;
CREATE TABLE CISCOWORKS
(
CISCOWORKS_IDNUMBER(9,0),
CISCOWORKS_MACVARCHAR2(20 BYTE),
CISCOWORKS_SWITCHVARCHAR2(10 BYTE),
CISCOWORKS_PORTVARCHAR2(10 BYTE),

[code].....

I guess I will have to check the INSERTED table and check every entry to see if there is a entry in the CISCOWORKS table with the same CISCOWORKS_MAC. If so, insert an entry in CISCOWORKS_HISTORY with the attributes of the CISCOWORKS entry, and then delete the CISCOWORKS entry. how would I check every entry separately?

View 4 Replies View Related

Client Tools :: Logon Trigger Creates 2 Records?

Aug 5, 2010

why my logon trigger always creates 2 records with different timestamp. Also is this the proper way of excepting records to insert?

Script.

CREATE OR REPLACE TRIGGER TR_LOGON_AUDIT
AFTER LOGON ON DATABASE
BEGIN
If user<> 'DBSNMP' then -- don't want to insert this
if user <> 'SYS' then -- don't want to insert this

[code]....

Result when select.

OS_USERNAME LOGON_TIMESTAMP
----------- ----------------
GAN 2010-08-05 14:27:52
GAN 2010-08-05 14:27:55

View 12 Replies View Related

Forms :: Updating Records Values And Post Trigger

Jul 15, 2013

I have a multi record block based on a view. All records in the view are displayed in the block by use of Post-Query trigger when entering the form.

The block has 5 items as follows:

1) RECORD_STATUS = a non-base table column which is a checkbox.
2) ITEM_TYPE = a text-item which has an LOV attached.
3) ITEM_TEXT = a text-item which is free format text.
4) LAST_UPDATE_DATE a date column
5) STATUS = a text item either 'Open' or 'Closed'

The LOV is based on a table of Item Types with values say, 'Type1', upto 'Type9'.

I have a Wnen-New-Record-Instance trigger which 'Posts' changes to the database. This has been included as i want to limit the values of the ITEM_TYPE column to values which have not been previously used.

Consider this scenario...

The block has 3 records.

record 1 has 'Closed' status so no updates are allowed.
record 2 has 'Open' status so updating of Item_Text is allowed.
record 3 has 'Open' status so updating of Item_Text is allowed.

I check the RECORD_STATUS checkbox on record2.

(This sets the RECORD_STATUS checkbox to a checked value and changes the STATUS column to 'Closed' by When-Checkbox-Changed trigger.) At this point the record has not been saved so if you uncheck the checkbox , then the STATUS column will go back to 'Open'. However at this point i will leave it as Checked (Closed).

I then insert a new record, only values Item4 to Item 9 are correctly shown in the LOV. I select Item4.

I then go back to the previous record and uncheck the Checkbox to say that i wish to leave it 'Open' after all (in effect no changes have occurred), then the STATUS column correctly reverts back to 'Open' by my WCC trigger. If i then SAVE the changes, the new record has been inserted on the database correctly, however the LAST_UPDATED_DATE from the record which was checked and then unchecked has also been updated incorrectly even though no net changes have actually occurred.

(because i am using WNRI trigger to limit the List of Values on the LOV column, this has incorrectly set the previous records LAST_UPDATED_DATE column to be Sysdate.)

How can i stop this from happening?

View 1 Replies View Related

SQL & PL/SQL :: How To Create The Trigger

Jan 28, 2013

how to create the trigger that count the last 7 day about from sale table if the last 7 day amount is greater then 10000 then update the commission 2% other wise do nothing

View 19 Replies View Related

PL/SQL :: Can't Create A Trigger

May 9, 2013

I cant create a trigger for the following problem "write a trigger to change the "hiredate"(in emp tale) in the default format even if the user enters it in some other format, say year/mon/date..."

View 7 Replies View Related

SQL & PL/SQL :: Create Trigger For Timestamp

Aug 13, 2010

Create a trigger that stamps the date_created and the date_updated column with current date on new and updated records?

View 2 Replies View Related

Forms :: How To Create A Trigger For Age

Apr 16, 2010

How do I create a trigger for the age. I want an alert to appear when the date of birth field is less than the age of 17. The formula is age=sysdate-dateofbirth

View 11 Replies View Related

SQL & PL/SQL :: Create A Sequence In DML Trigger?

Apr 12, 2013

i would like to create a sequence inside a dml trigger. Is it possible? I created a trigger and it is compiled sucessfully.

create or replace trigger tri_update_test
after delete on test
declare
pragma autonomous_transaction;

[code]...

trigger created sucessfully.And i try to delete data from the test

delete from test where id=5;

Output:

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.TRI_UPDATE_TEST", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRI_UPDATE_TEST'

View 4 Replies View Related

SQL & PL/SQL :: Create A Trigger And Procedure To Go Along With DDL

May 11, 2010

I'm trying to create a trigger and procedure to go along with my DDL. Everything is created just fine, but when I try to execute an update on the table monitored by the trigger I get the following error:

update housing set group_num = 1 where room_num = 10

ERROR at line 1:
ORA-00937: not a single-group group function
ORA-06512: at "YANKEEFAN146.VIOLATION_CHK", line 6
ORA-04088: error during execution of trigger 'YANKEEFAN146.VIOLATION_CHK'

/* DDL */
create table violation_type
(violation_num number(1) primary key,
violation_def varchar2(100) not null)
organization index
tablespace mi257_data;
[code]....

View 9 Replies View Related

Create After Logon Trigger

Jun 20, 2012

I'm looking to create an after logon trigger... my question is - can this trigger be created by any user (i.e. other than sys) and - if so - what's required to ensure it fires for EVERY other user that log's on to the database?

View 2 Replies View Related

SQL & PL/SQL :: Create Table Using Trigger

Feb 1, 2013

Is it possible to create table using trigger?

I tried but failed...

CREATE OR REPLACE TRIGGER tri1
AFTER UPDATE
ON dept
FOR EACH ROW
declare
pragma AUTONOMOUS_TRANSACTION;
BEGIN
execute immediate 'create table dept_dum as select * from dept';
END;
/

SQL> update dept set deptno=23 where deptno=10;
update dept set deptno=23 where deptno=10
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.TRI1", line 6
ORA-04088: error during execution of trigger 'SCOTT.TRI1'

I am running this as scott user

My requirement is to

create table as select * from table_name where flag=1

This has to be done parallel for all the tables for which this flag is enabled and by that trigger delete all those rows which were backed up as table.

View 4 Replies View Related

Create One Trigger Before DML On All Schema Tables

Nov 28, 2011

We have an application is must be connected to our database for specific requirements in our company but this application has a very bug thing as we must write the super DB password "Like HR password as example" clear in some files and these files must be shared so developers can use the HR password to do any action !!! I know that this application is a problem but we have to install

I can do this by creating trigger on each table will restrict DML. As example: if the operating system user is XXX, the trigger restrict the action. But not logic at all to create more than 1000 triggers on schema (This will impact badly on DB performance).

So, i need to create one trigger to fire before doing any DML on all schema tables. As example: If "MMM" the administrator operating system user trying to do insert action, he can do the action. BUT If "DEV" the developer operating system user trying to do insert action, the trigger must fire here to restrict this action.

Be noted also, i need this trigger not depend on any specific tool like Toad as any user can simply rename the exe file for toad then he can pass the trigger. At least, trigger must depend on (Operating system user & Action_type)

View 11 Replies View Related

Privileges Of Create Trigger - Procedures?

Aug 16, 2013

i want to give privilege of create trigger,procedures and functions privileges to a user "A"on the schema "B". how can i do it. i've already given select,insert,update,delete privilege to user "A"

how can user "A" create trigger(etc.) on tables of user "B".

View 1 Replies View Related

Forms :: How To Create Trigger On Backend

Oct 14, 2010

I Created a form just for testing purpose [Which is i attached]

My Table is TEST0

Structure of table :
---------------------------
NOVARCHAR2(5)
NAME VARCHAR2(10)
FLG VARCHAR2(1)

My Task is When i added a record, e.g if on form, i add name - AAD But its must save on TEST0 Table like this way EMPAAD...means always concate with 'EMP'||:Block.name while saving on database..

I know how to do with front end application, but i want to achieve this task by using trigger on backend databse...how to create a Trigger on backend....i try a one trigger but it give me error- Unable to insert Record...

View 12 Replies View Related

SQL & PL/SQL :: Trigger Insert - Create Or Replace?

Jun 12, 2013

I have make a new trigger.Create a trigger that inserting a new job_id MAX_SALARY assigned as the employee's salary more than 80 departmental charges

I have that code, is that correct?

CREATE OR REPLACE
TRIGGER TR27
AFTER INSERT ON JOBS FOR EACH ROW
BEGIN
(SELECT MAX(SALARY) FROM EMPLOYEES WHERE DEPARMENT_ID=80);
:NEW.MAX_SALARY := :OLD.MAX_SALARY;
END;

What I need to complete it?

View 2 Replies View Related

SQL & PL/SQL :: Create A Trigger On Multiple Schemas

May 25, 2011

I have following schemas in my database scott,sh,hr...I created a triger on a schema scott with the following method

1. sqlplus / as sysdba

2. CREATE TABLE LOGIN_AUDIT_INFO_ALL (
operation VARCHAR2(30),
obj_owner VARCHAR2(30),
object_name VARCHAR2(30),
sql_text VARCHAR2(64),
attempt_by VARCHAR2(30),
attempt_dt DATE);

3.CREATE OR REPLACE TRIGGER login_audit_prod_schemas
AFTER CREATE OR ALTER OR DROP
ON SCOTT.SCHEMA
[code]....

But it don't fulfill my requirement. trigger is fired when scott perform any action. but my requirment is that trigger should be fired when create action is performed on scott by hr,sh or scott. i also want a single trigger which fulfill the requirement.

View 8 Replies View Related

SQL & PL/SQL :: Create A Trigger With After Logon Database?

Apr 28, 2011

i would like to trace some connexions on database, once compile the code , i have the issus following:

10/2 PL/SQL: SQL Statement ignored
11/20 PL/SQL: ORA-00942: table or view does not exist

this is the

DROP TABLE connect_user;
CREATE TABLE connect_user
(
nom_ora varchar2(15),
naom_os varchar2(15),
programme varchar2(20),
poste varchar2(20),

[code]...

View 3 Replies View Related

SQL & PL/SQL :: Create Trigger On Table After Update?

Apr 16, 2013

i have created a trigger on a table after update. i am using if condition if the condition is true i am passing a value and then inserting into audit table.

if(condtion)
L_change_type='value'
end if;
if(condtion)
L_change_type='value'
end if;
if(condtion)
L_change_type='value'
end if;
if(condtion)
L_change_type='value'
end if;

Then i am inserting

Insert into audit_table(change_type.....)
values(..L_change_type)...)

If i want to skip the insert statement for particular condition wt i have to do.

View 7 Replies View Related

SQL & PL/SQL :: Unable To Create Trigger Dynamically

Dec 3, 2010

I have created a procedure to build trigger dynamically using Dynamic SQL. Here procedure created successfully, when we execute the procedure to build trigger getting the following error.

Note: We are able to create a Trigger, but it is INVALID. if we get the code of a trigger and execute, trigger created a successfully with Valid Status.

ERROR:
ORA-24344: success with compilation error
ORA-06512: at "APPS.CREATE_TRIGGER", line 28
ORA-06512: at line 2

SQL*Plus internal error state 2087, context 47:0:0
Unsafe to proceed

View 2 Replies View Related

SQL & PL/SQL :: Create Test Table - Instead Of Trigger

Dec 30, 2010

table
CREATE TABLE "TEST"
("CONTRACT_REF_NO" VARCHAR2(20 BYTE) primary key,
"CHG_GL" VARCHAR2(9 BYTE),
"CHG_GL_1" VARCHAR2(9 BYTE),
"CHG_GL_2" VARCHAR2(9 BYTE),

[code]...

10 rows selected.

create or replace view test_view
as
select 1 id,CONTRACT_REF_NO,chg_gl
from test
union all

[code]...

11 rows selected.

I have done only for one id.

so the problem is the front end is showing this test_view that people will modify.but they have to actually modify the table ,so I can think of an instead of trigger(instead of insert and instead of update ones).

View 16 Replies View Related

SQL & PL/SQL :: To Create A Trigger To Prevent Transactions

May 10, 2013

Im trying to create a trigger to prevent transactions like insert, update and delete based on two conditions:

1) Friday
2) Between 5pm to 8am

View 5 Replies View Related

Possible To Create Sequence Inside DML Trigger

Apr 12, 2013

i would like to create a sequence inside a dml trigger. Is it possible? I created a trigger and it is compiled sucessfully.

create or replace trigger tri_update_test
after delete on test
declare
pragma autonomous_transaction;
begin
execute immediate 'create sequence t_unqid
start with 1
increment by 1
nocache '

end ;trigger created successfully. And i try to delete data from the test delete from test where id=5;

Output:

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.TRI_UPDATE_TEST", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRI_UPDATE_TEST'

View 9 Replies View Related

SQL & PL/SQL :: Create Trigger On (account) Table?

Nov 23, 2008

I gave this a shot on my own, and failed, so I am putting the question to you:

I am looking to create a trigger on the "Account" table

CREATE TABLE Account(Acct_no number, BillAmt number, OpenDate Date, SSN number,
Primary Key (Acct_no),
Foreign Key(SSN) references ResponsibleParty(SSN));

which will update the BillAmt field whenever one of the account's respective records is inserted/updated/deleted from the following tables:

CREATE TABLE Plan(Plan_id number, Cost number, Minutes number,
primary key(Plan_id));
CREATE TABLE Feature(Feature_id number, Description varchar(20), Cost number,
primary key(Feature_id));

These tables are linked through the following table:

CREATE TABLE Phone_Line(MTN number, cStart Date, cEnd Date,
rDate Date, Status char, ESN number, Acct_no number, Plan_id number,
primary key(MTN),
foreign key(ESN) references Equiptment(ESN), foreign key( Acct_no) references Account(Acct_no),
foreign key(Plan_id) references Plan(Plan_id));

View 26 Replies View Related

Create Trigger To Stop Events From Being Deleted

Jun 2, 2010

I am trying to create a trigger to stop events from being deleted if the date is before the current date. This is the SQL syntax for my table

CREATE TABLE event
(event_id numeric(5) not null,
concert_id numeric(5) not null,
event_date date,
);

What I have attempted so far is:

CREATE OR REPLACE TRIGGER event_deleting
BEFORE DELETE ON event
FOR EACH ROW
BEGIN
IF DELETING THEN
[code]......

This gives me a few warnings, the first is that a DECLARE is missing and the second is a INTO is missing.

View 1 Replies View Related

Create Trigger That Can Control Startdate And EndDate?

Apr 30, 2013

create table TestDate(
idDate int not null primary key,
StartDate date not null,
EndDate date not null
)

I want to create a trigger that can control the Startdate and the EndDate when inserting data,the Endate must be less than the StartDate

View 1 Replies View Related

SQL & PL/SQL :: CREATE OR REPLACE - Error During Execution Of Trigger

Aug 9, 2011

I create this trigger:

CREATE OR REPLACE TRIGGER T_HST AFTER INSERT
On HST
FOR EACH ROW
DECLARE
Begin
INSERT INTO HST (EMP_NO, START_DATE )
SELECT EMP_NO, SYSDATE
FROM HST
WHERE EMP_NO = :NEW.EMP_NO;
End T_HRS_SAL_PERIOD_HSTY_UPD_PRI;
/

when I insert a new record in table HST I get this error:

ORA-04091: table HST is mutating, trigger/function may not see it
ORA-06512: at "T_HST", line 3
ORA-04088: error during execution of trigger 'T_HST'

What I wrong in this trigger?How can I modify it?

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved