PACKAGE and TRIGGERS

PACKAGE:
A package is a schema object that groups logically related PL/SQL types,
variables, constants, subprograms, cursors, and exceptions. A package is
compiled and stored in the database, where many applications can share its
contents.
In PL/SQL, a package is a schema object that contains definitions for a group of

related functionalities. A package
includes variables, constants, cursors, exceptions, procedures, functions, and
subprograms. It is compiled and stored in the Oracle Database.
Typically, a package has a specification and a body. A package specification is

mandatory while the package body can be required or optional, depending on the
package specification.
Package specification
The package specification declares the public objects that are accessible from
outside the package.
If a package specification whose public objects include cursors and subprograms,

then it must have a body which defines queries for the cursors and code for the
subprograms.
Package body
A package body contains the implementation of the cursors or subprograms

declared in the package specification. In the package body, you can declare or define
private variables, cursors, etc., used only by package body itself.
A package body can have an initialization part whose statements initialize variables
or perform other one-time setups for the whole package.
A package body can also have an exception-handling part used to handle exceptions.
The following picture illustrates PL/SQL packages:

Using PL/SQL packages:
The package is a powerful feature of PL/SQL that you should use it in any project.
The following are the advantages of the package:
Make code more modular
Packages allow you to encapsulate logically related types, variables, constants,

subprograms, cursors, and exceptions in named PL/SQL modules. By doing this, you
make each package more reusable, manageable, readable and reliable.
Hide implementation details
Packages allow you to expose the functionality via their specifications and hide the
detailed implementation in the package body.
It means that you can enhance the code in the body of the package without affecting
other dependent packages or applications.
Improve application performance
Oracle loads the package into memory at the first time you invoke a package
subprogram. The subsequent calls of other subprograms in the same package do not
require disk I/O. This mechanism helps improve performance.
Minimize unnecessary recompiling code
Packages help avoid the unnecessary recompiling process. For instance, if you
change the body of a package function, Oracle does not recompile the subprograms
that use the function, because the subprograms are only dependent on the package
specification, not the package body.
Manage authorization easily
By encapsulate objects in a package, you grant role on the package, instead of

granting roles on each object in the package.
PL/SQL package concept is simple but powerful. They allow you to encapsulate the
code and make your application easier to develop and maintain.
Triggers:
A trigger is a special type of stored procedure that automatically runs when an event
occurs in the database server. DML triggers run when a user tries to modify data
through a data manipulation language (DML) event. DML events are INSERT,
UPDATE, or DELETE statements on a table or view.
The trigger is a database object similar to a stored procedure that

is executed automatically when an event occurs in a database.
There are different kinds of events that can activate a trigger like
inserting or deleting rows in a table, a user logging into a database
server instance, an update to a table column, a table is created,
altered, or dropped, etc.
For example, consider a scenario where the salary of an employee

in the Employee table is updated. You might want to preserve the
previous salary details in a separate audit table before it gets
updated to its new value. You can create a trigger to automatically
insert updated employee data to the new audit table whenever
the Employee table's value is updated.
There are three types of triggers in SQL Server
 DML triggers are automatically fired when an INSERT,

UPDATE or DELETE event occurs on a table.
 DDL triggers are automatically invoked when a CREATE,
ALTER, or DROP event occurs in a database. It is fired in
response to a server scoped or database scoped event.
 Logon trigger is invoked when a LOGON event is raised when
a user session is established.
DML Triggers:
DML (Data Manipulation Language) trigger is automatically invoked

when an INSERT, UPDATE or DELETE statement is executed on a
table.
se the CREATE TRIGGER statement to create a trigger in SQL

Server.
Syntax: Create Trigger

Copy
CREATE TRIGGER [schema_name.]trigger_name
ON { table_name | view_name }
{ FOR | AFTER | INSTEAD OF } {[INSERT],[UPDATE],
[DELETE]}
[NOT FOR REPLICATION]
AS
{sql_statements}
DDL Triggers in SQL Server
GRANT, DENY, REVOKE, or UPDATE STATISTICS. For example, you can

define a DDL trigger that records CREATE or ALTER TABLE operations.
DDL trigger fires only after the events that fired them are executed
successfully. They cannot be used as INSTEAD OF triggers .
You can create a DDL trigger to:
 Log changes made to the database schema;

 Prevent certain changes to the schema; DDL triggers respond to
DDL events like CREATE, ALTER, DROP,
 To respond to any change in the database schema.
Syntax: DDL Trigger

Copy
CREATE TRIGGER trigger_name
ON { DATABASE | ALL SERVER}
[WITH ddl_trigger_option]
FOR { event_type | event_group }
AS
{sql_statement}
LOGON Triggers in SQL Server:
In SQL Server, the Logon trigger is fired automatically on a LOGON

event. They are DDL triggers and are created at the server level. We
can define more than one LOGON trigger on a server.
A LOGON trigger can be used in controlling server sessions by tracking

login activity, restricting logins to the SQL Server, or limiting the
number of sessions for a particular login.
The following LOGON trigger restricts the login attempt to SQL Server
by sa login if there are already two user sessions created by that login.

PACKAGE and TRIGGERS

Uploaded by

Copyright:

Available Formats

PACKAGE and TRIGGERS

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

PACKAGE and TRIGGERS

Uploaded by

Copyright:

Available Formats

PACKAGE:

In PL/SQL, a package is a schema object that contains definitions for a group of

Typically, a package has a specification and a body. A package specification is

If a package specification whose public objects include cursors and subprograms,

A package body contains the implementation of the cursors or subprograms

A package body can also have an exception-handling part used to handle exceptions.

The following picture illustrates PL/SQL packages:

Make code more modular

Packages allow you to encapsulate logically related types, variables, constants,

Hide implementation details

Improve application performance

Minimize unnecessary recompiling code

Manage authorization easily

By encapsulate objects in a package, you grant role on the package, instead of

The trigger is a database object similar to a stored procedure that

For example, consider a scenario where the salary of an employee

There are three types of triggers in SQL Server

 DML triggers are automatically fired when an INSERT,

DML (Data Manipulation Language) trigger is automatically invoked

se the CREATE TRIGGER statement to create a trigger in SQL

Syntax: Create Trigger

GRANT, DENY, REVOKE, or UPDATE STATISTICS. For example, you can

You can create a DDL trigger to:

 Log changes made to the database schema;

Syntax: DDL Trigger

LOGON Triggers in SQL Server:

In SQL Server, the Logon trigger is fired automatically on a LOGON

A LOGON trigger can be used in controlling server sessions by tracking

You might also like