SQL Hello I need help creating Trigger and Stored Procedures base on this table

Question

SQL

Hello I need help creating Trigger and Stored Procedures base on this table thank you in advance

?EPLOYEE-02 3 Start Page Columns Data Model Constraints Grants Statistics Triggers Flashback lDependencies Details Partitions Indexes IsQL . OL ? Actions.. ?NULLABLE DATA DEFAULT COLUM.ID |?COMMENTS DATA TYPE VARCHAR2 (30 BYTE) No VARCHAR2 (30 BYTE) No COLUMN NAME 1 EMP FNAME 2 EMP LNAME 3 EMP HOME_PHONE VARCHAR2 (15 BYTE) No 4EMP MOBILE 5 EMP_EMAIL 6 EMP ADDRESS VARCHAR2 (100 BYTE) No 7 EMP_2IP CODE VARCHAR2 (10 BYTE) No 8 EMP CITY 9 HIRE_DATE 10 END DATE 11 SALARY 12 STATE ID 13 EMP STATUS 14 EMP TYPE ID 15 COUNIRY ID 16 EMPLOYEE ID (null) (null) (null) (null) (null) (null) (null) (null) (null) (null) (null) (null) (null) (null) (null) (null) 1 (null) 2 (null) 3 (null) 4 (null) 5 (null) (null) 7 (null) 8(null) 9 (null) 10 (null) 11 (null) 12 (null) 13 (nul1) 14 (nul1) 15 (null) 16 (null) VARCHAR2 (15 BYTE) Yes VARCHAR2 (100 BYTE) No VARCHAR2 (40 BYTE) Yes DATE DATE NUMBER NUMBER VARCHAR2 (50 BYTE No NUMBER NUMBER NUMBER No Yes No No No No No

Explanation / Answer

Trigger is an individual sort of a stored procedure so as to perform, in reaction to certain act on the table like insertion, deletion or updating of data. It is a database object which is vault to a table as well as is executed routinely. We can’t overtly call upon triggers. DML Triggers are used to assess data after data manipulation using DML statements.

There are basic two types of triggers:

|| First create table for Employee_02 ||

CREATE TABLE Employee_02

(

Emp_FNAME VARCHAR2(30),

Emp_LNAME VARCHAR2(30),

Emp_HOME_PHONE VARCHAR2(15),

Emp_MOBILE VARCHAR2(15),

Emp_EMAIL VARCHAR2(100),

Emp_ADDRESS VARCHAR2(100),

Emp_ZIP_CODE VARCHAR2(10),

Emp_CITY VARCHAR2(40),

HIRE_DATE DATE,

END_DATE DATE,

SALARY NUMBER,

STATE_ID NUMBER,

EMP_STATUS VARCHAR2(50),

EMP_TYPE_ID NUMBER,

COUNTRY_ID NUMBER,

EMPLOYEE_ID NUMBER,

)

-- Now Insert records USING “INSERT INTO” command

Insert into Employee_02 values ();

||   INSERT THE VALUES AS PER YOUR REQUIREMENT ||

Now use the after insert trigger:

This trigger is fired after an INSERT on the table. Let’s create the trigger as:

CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_02]

FOR INSERT

AS

declare @empfname varchar2(30);

declare @emplname varchar2(30);

declare @emphomephn varchar2(15);

declare @empmobile varchar2(15);

declare @empemail varchar2(100);

declare @empaddress varchar2(100);

declare @empzipcode varchar2(10);

declare @empcity varchar2(40);

declare @hiredate date;

declare @enddate date;

declare @salary number;

declare @stateid number;

declare @empstatus varchar2(50);

declare @emptypeid number;

declare @countryid number;

declare @employeeid number;

select @empfname=i.Emp_FNAME from inserted i;

select @emplname=i.Emp_LNAME from inserted i;

select @emphomephn=i.Emp_HOME_PHONE from inserted i;

select @empmobile=i.Emp_MOBILE from inserted i;

select @empemail=i.Emp_EMAIL from inserted i;

select @empaddress=i.Emp_ADDRESS from inserted i;

select @empzipcode=i.Emp_zip_CODE from inserted i;

select @empcity=i.Emp_CITY from inserted i;

select @hiredate=i.HIRE_DATE from inserted i;

select @enddate=i.END_DATE from inserted i;

select @salary=i.SALARY from inserted i;

select @stateid=i.STATE_ID from inserted i;

select @empstatus=i.Emp_STATUS from inserted i;

select @emptypeid=i.Emp_TYPE_ID from inserted i;

select @countryid=i.COUNTRY_ID from inserted i;

select @empid=i.EMPLOYEE_ID from inserted i;               

insert into Employee_02

         (Emp_FNAME, Emp_LNAME, Emp_HOME_PHONE, Emp_MOBILE, Emp_EMAIL, Emp_ADDRESS, Emp_zip_CODE, Emp_CITY, HIRE_DATE, END_DATE, SALARY, STATE_ID, Emp_STATUS, Emp_TYPE_ID, COUNTRY_ID)

                values(@empfname, @emplname , @emphomephn , @empmobile , @empemail , @empaddress , @empzipcode , @empcity , @hiredate , @enddate, @salary , @stateid, @empstatus, @emptypeid, @countryid, @employeeid, getdate());

                PRINT 'AFTER INSERT trigger fired.'

GO

In the trigger body, table named inserted has been used. This table is a logical table and contains the row that has been inserted. I have selected the fields from the logical inserted table from the row that has been inserted into different variables, and finally inserted those values into the Employee_02 table.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.