If i insert record in table A and these record should update in table B by using Trigger.How to achieve this.
Answers were Sorted based on User's Feedback
Answer / anil
CREATE table trg_tab
(ename VARCHAR(10),
sal NUMBER);
/
CREATE table log_tab
(usr VARCHAR(10),
upd DATE,
ename VARCHAR(10),
sal NUMBER
);
/
CREATE OR REPLACE TRIGGER log_trg
AFTER INSERT ON trg_tab
FOR EACH ROW
DECLARE
v_user VARCHAR(10);
BEGIN
SELECT USER
INTO v_user
FROm DUAL;
INSERT INTO log_tab
VALUES (v_user,SYSDATE,:NEW.ename,:NEW.sal);
END;
/
INSERT INTO trg_tab
SELECT ename,sal FROm emp;
/
SELECT * FROm log_tab;
/
Is This Answer Correct ? | 7 Yes | 1 No |
Answer / abhishek jaiswal
Table 1(U_register)
Name Null? Type
----------------------------------------- -------- ----------------------------
FIRST_NAME PK VARCHAR2(15)
LAST_NAME NOT NULL VARCHAR2(15)
DOB NOT NULL DATE
USER_NAME NOT NULL VARCHAR2(15)
NEW_PASSWORD NOT NULL VARCHAR2(15)
CONFIRM_PASSWORD NOT NULL VARCHAR2(15)
U_ID NOT NULL NUMBER
Table 2(Login_detail)
Name Null? Type
-------------------------FK---------------- -------- ----------------------------
ID NOT NULL NUMBER
USER_NAME VARCHAR2(15)
NEW_PASSWORD VARCHAR2(15)
Now,We have to write trigger to insert of column 'user_name' and 'new_password' in login_detail table.
To do this we will use After insert trigger.
create or replace
trigger tgr_login
after insert on u_register
for each row
begin
insert into login_detail
values (:new.u_id,:new.user_name,:new.new_password);
end tgr_login;
---and It will work .
Is This Answer Correct ? | 2 Yes | 0 No |
Table 1(u_register) Parent table
Name Null? Type
----------------------------------------- -------- ----------------------
FIRST_NAME VARCHAR2(15)
LAST_NAME NOT NULL VARCHAR2(15)
DOB NOT NULL DATE
USER_NAME NOT NULL VARCHAR2(15)
NEW_PASSWORD NOT NULL VARCHAR2(15)
CONFIRM_PASSWORD NOT NULL VARCHAR2(15)
U_ID NOT NULL NUMBER
table 2(login_detail) Child table
Name Null? Type
----------------------------------------- -------- ----------------------
ID NOT NULL NUMBER
USER_NAME VARCHAR2(15)
NEW_PASSWORD VARCHAR2(15)
Now we will create trigger to insert column 'user_name','new_password' of u_register into column 'USER_NAME ','NEW_PASSWORD' of login_detail.We will use after insert trigger as
create or replace
trigger tgr_login
after insert on u_register
for each row
begin
insert into login_detail
values (:new.u_id,:new.user_name,:new.new_password);
end tgr_login;
It will work for sure.
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / shwetha
CREATE TABLE A1(ID NUMBER(2),NAMES VARCHAR2(30))
CREATE TABLE B1(ID NUMBER(2),NAMES VARCHAR2(30),STATUS VARCHAR2(10))
CREATE OR REPLACE TRIGGER A1B1
AFTER INSERT ON A1
FOR EACH ROW
BEGIN
INSERT INTO B1 VALUES(:NEW.ID, :NEW.NAMES,'Y');
END;
Is This Answer Correct ? | 0 Yes | 0 No |
What is cursor in pl sql?
scope of exception handling in plsql
What is set serveroutput on?
What are primary key and foreign key and how they work?
What action do you have to perform before retrieving data from the next result set of a stored procedure ?
What is sql partition function?
Is sql a microsoft product?
What is crud sql?
Write the order of precedence for validation of a column in a table? I. Done using database triggers. Ii. Done using integarity constraints
What is differance unique key and primary key.
What do you mean by stored procedures? How do we use it?
Can we create view in stored procedure?