CURSOR

The cursor is in SQL a temporary work area created in the system memory when a SQL statement is executed. The cursor contains information on a select statement and the rows of data accessed by it. These temporary work areas are used to store the data to retrieve from the database and manipulate this data. The cursor can hold more than one row, but it can process only one row at a time. A set of rows the cursor holds is called the active set. We use the cursor when we need to update records in a database table in singleton fashion means row by row.

These are two types of CURSORs like –

1. Implicit cursors
2. Explicit cursors

Implicit CURSORS:-

The cursor is created itself by default when DML (data manipulation language) statements like, INSERT, UPDATE, and DELETE statements are executed. These are also created when a SELECT statement that returns just one row is executed.

Explicit CURSOR:-

The cursor must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called the current row. If you exclude a row the current row position moves to the next row.

These are both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed.

An Assertion in SQL:- The assertion is a piece of SQL which makes sure a condition is satisfied or it stops action being taken on a database object. It could mean locking out the whole table or even the whole database.

1). The assertion in SQL is a predicate expressing a condition we wish the database to always satisfy.

2). These are like Domain constraints, functional dependency and referential integrity are special forms of assertion.

3). When a constraint cannot be expressed in these forms, we use an assertion, e.g.

•  Ensuring the total of loan amounts for each branch is less than the sum of all account balances at the branch.
•  Ensuring every loan customer keeps a minimum of 1000/- in an account.

create assertion  assertion-name check predicate

4). Whereas an assertion is created, the system tests it for validity.

• If the assertion is valid, any further modification to the database is allowed only if it does not cause that assertion to be violated.
• These testing may result in significant overhead if the assertions are complex. Therefore of this, the assert should be used with great care.

5). A few system developers omit support for general assertions or provide a specialized form of assertions that are easier to test.

CREATE ASSERTION <constraint name> CHECK (<search condition>

CREATE VIEW vw_AssertionOneValidContract

AS
  SELECT ID, ClientID, Title, ValidFrom, ValidTo
    FROM Contract
   WHERE ID NOT IN (SELECT ID FROM chk_AssertionOneValidContract)
   WITH CHECK OPTION 
GO

Trigger : - The trigger is a piece of SQL to execute either before or after an update, insert, or delete in a database. The SQL trigger is a set of SQL statements stored in the database catalog. The SQL trigger is executed or fired whenever an event associated with a table occurs e.g., insert, update or delete. The SQL trigger is a special type of stored procedure. This is special because it is not called directly like a stored procedure. The primary difference between a trigger and a stored procedure is that a trigger is called automatically when a data modification event is made against a table whereas a stored procedure must be called explicitly.

The example of a trigger in plain English might be something like:

Before updating a customer record, save a copy of the current record.

Which would look something like:

      CREATE TRIGGER triggerName

     AFTER UPDATE

    INSERT INTO CustomerLog (hi, hi, hi)

    SELECT blah, blah, blah FROM deleted

There are two types of Triggers:

• DDL Trigger
• DML trigger

CREATE TRIGGER Alert_1  

ON Customers  

AFTER INSERT, UPDATE   

AS RAISERROR ('Notify Customer Relations', 16, 10);  

GO

CREATE TRIGGER Alert_2  

ON Customers  

AFTER INSERT, UPDATE, DELETE   

AS  

   EXEC msdb.dbo.sp_send_dbmail  

        @profile_name = 'Mindstick Developer',  

        @recipients = 'sanat@mindstick.com',  

        @body = 'Don’t forget to print a report for the sales force.’  

        @subject = 'Reminder';  

GO  

VIEW - A SQL VIEW is, in essence, a virtual table that does not physically exist. By virtual, we mean, the tables do not store any data of their own but display data stored in other tables. Formerly, it is created by a SQL statement that joins one or more tables. The VIEW in SQL is a logical subset of data from one or more tables. The view is used to restrict data access. The VIEW is as a virtual table, through which a selected portion of the data from one or more tables can be seen. The Views do not contain data of their own. These are used to restrict access to the database or to hide data complexity. The view is stored as a SELECT statement in the database. The DML operations on a view like INSERT, UPDATE, DELETE affects the data in the original table upon which the view is based. Into the view mostly used with JOINs.

There are two types of view - 

•  Simple View
•  Complex View

  CREATE or REPLACE VIEW sale_view 

     AS 

     SELECT * FROM Sale WHERE customer = 'Ram';

  CREATE VIEW sup_orders AS

  SELECT suppliers.supplier_id, orders.quantity, orders.price

  FROM suppliers

  INNER JOIN orders

  ON suppliers.supplier_id = orders.supplier_id

  WHERE suppliers.supplier_name = 'Dell';