KEMBAR78
View, Trigger, Procedure | PDF | Information Technology | Sql
Scribd
Upload
4 views5 pages

View, Trigger, Procedure

The document outlines key SQL concepts including GRANT and REVOKE for managing user privileges on database objects, as well as the creation and use of VIEWS and TRIGGERS for data manipulation and tracking. It provides examples for granting privileges, creating views, and defining triggers for automatic actions on table events. Additionally, it explains PROCEDURES as stored blocks of code for executing SQL statements.

Uploaded by

anlabibofficial
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views5 pages

View, Trigger, Procedure

The document outlines key SQL concepts including GRANT and REVOKE for managing user privileges on database objects, as well as the creation and use of VIEWS and TRIGGERS for data manipulation and tracking. It provides examples for granting privileges, creating views, and defining triggers for automatic actions on table events. Additionally, it explains PROCEDURES as stored blocks of code for executing SQL statements.

Uploaded by

anlabibofficial
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 5

1.

GRANT
Purpose:​
Used to give users access (privileges) to perform actions on database
objects like tables, views, etc.

GRANT privilege_name ON object_name TO user_name;

Example:

Let’s say there’s a table called employees, and we want to allow user
JOHN to SELECT data from it.

GRANT SELECT ON employees TO JOHN;

Now, JOHN can run:

SELECT * FROM employees;

You can also grant multiple privileges:

GRANT SELECT, INSERT ON employees TO JOHN;


2. REVOKE
Purpose:​
Used to take away the privileges that were previously granted.

REVOKE privilege_name ON object_name FROM user_name;

Example:

Remove the SELECT access from JOHN:

REVOKE SELECT ON employees FROM JOHN;

3. VIEW
Purpose:​
A VIEW is a virtual table created using a SELECT query. It does not
store data itself but shows data from one or more tables.

CREATE VIEW view_name AS


SELECT columns FROM table WHERE condition;

Example:

Create a view that shows only employee names and salaries:

CREATE VIEW emp_salary_view AS


SELECT first_name, salary
FROM employees;
Use the view like a table:

SELECT * FROM emp_salary_view;

4. TRIGGER
Purpose:​
A TRIGGER automatically performs an action when a specific event
happens on a table (like INSERT, UPDATE, DELETE).

When an employee’s salary is updated, a trigger will automatically


insert a record into a salary_history table to keep track of changes.

CREATE TABLE employees (


employee_id NUMBER,
first_name VARCHAR2(50),
salary NUMBER
);

CREATE TABLE log_table (


log_id NUMBER GENERATED ALWAYS AS IDENTITY,
action_type VARCHAR2(50),
emp_id NUMBER,
log_time DATE
);
1. BEFORE Trigger Example
Ensure salary is never negative before inserting.

CREATE OR REPLACE TRIGGER trg_before_insert_emp


BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be
negative.');
END IF;
END;

2. AFTER Trigger Example

After an employee is inserted, write an entry into log_table.

CREATE OR REPLACE TRIGGER trg_after_insert_log

AFTER INSERT ON employees

FOR EACH ROW

BEGIN

INSERT INTO log_table (action_type, emp_id, log_time)

VALUES ('INSERTED', :NEW.employee_id, SYSDATE);

END;
5. PROCEDURE
Purpose:​
A PROCEDURE is a stored block of code you can call to perform
actions, like a function in programming.

Syntax:
CREATE OR REPLACE PROCEDURE procedure_name
IS
BEGIN
-- SQL statements
END;

Example:

Create a procedure to display a message:

CREATE OR REPLACE PROCEDURE welcome_message


IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Welcome to Oracle SQL!');
END;

To run it:
EXEC welcome_message;

You might also like