MS SQL Server

The trigger, dml_message, provided in previous tutorials was defined to handle all 3 types of DML statements, INSERT, UPDATE, and DELETE.

If you do not want the trigger to handle all 3 types of DML statements, you can list only 1 or 2 of the statement keywords. For example, the following SQL script defines a trigger that only handle the INSERT statement events:

USE GlobalGuideLineDatabase

GO

CREATE TRIGGER new_user ON ggl_users

AFTER INSERT

AS

PRINT 'Time: '+CONVERT(VARCHAR(12),GETDATE())

+ ' New users added.';

GO

INSERT INTO ggl_users (name) VALUES ('Marc MHI');

GO

Time: Jul 1 2007

Records are inserted, updated, or deleted in ggl_users

Time: Jul 1 2007 New users added.

(1 row(s) affected)

If what are the DML events an existing trigger is handling, you can use the catalog view, sys.trigger_events. You need to join sys.trigger_events and sys.triggers to get a better list as shown in this tutorial example:

USE GlobalGuideLineDatabase

GO

SELECT t.name, e.type, e.type_desc

FROM sys.trigger_events AS e, sys.triggers AS t

WHERE e.object_id = t.object_id

GO

name   type type_desc

-------------- ------ ---------

dml_message 1 INSERT

dml_message 2 UPDATE

dml_message 3 DELETE

new_user  1 INSERT

(4 row(s) affected)

The list clearly shows that dml_message handles 3 events: INSERT, UPDATE and DELETE.

When a DML event occurs, SQL Server will prepare a temporary table called "INSERTED", which contains the new record of the affected row, which is:

* A copy of the inserted row for an INSERT statement.

* A copy of the updated row for an UPDATE statement.

* Empty for a DELETE statement.

The tutorial exercise below shows you how to create a trigger, update_user, to report email changes on table, ggl_users:

USE GlobalGuideLineDatabase;

GO

DISABLE TRIGGER dml_message ON ggl_users;

GO

CREATE TRIGGER update_user ON ggl_users

AFTER UPDATE

AS

DECLARE @new VARCHAR(80);

SELECT @new = email FROM INSERTED;

PRINT 'Email changed to '+@new;

GO

UPDATE ggl_users SET email='smith@GlobalGuideline'

WHERE name = 'John King';

GO

Email changed to smith@GlobalGuideline

(1 row(s) affected)

As you can see, the INSERTED table is helpful, if you want the trigger to perform specific logics on the affected rows.

When a DML event occurs, SQL Server will prepare a temporary table called "DELETED", which contains the old record of the affected row, which is:

* A copy of the deleted row for a DELETE statement.

* A copy of the row to be updated for an UPDATE statement.

* Empty for an INSERT statement.

The tutorial exercise below shows you how to improve the trigger, update_user, to report email changes on table, ggl_users, with both old and new emails:

USE GlobalGuideLineDatabase;

GO

ALTER TRIGGER update_user ON ggl_users

AFTER UPDATE

AS

DECLARE @new VARCHAR(80);

DECLARE @old VARCHAR(80);

SELECT @new = email FROM INSERTED;

SELECT @old = email FROM DELETED;

PRINT 'Email changed from '+@old+' to '+@new;

GO

UPDATE ggl_users SET email='king@ggl'

WHERE name = 'John King';

GO

Email changed from smith@GlobalGuideline to master@GlobalGuideline

(1 row(s) affected)

INSERTED and DELETED are working as expected. The reported message is getting better.

If there is only one row affected by a DML statement, we know that the DML trigger will be executed once. But how many times the DML trigger will be executed if the DML statement resulted multiple affected rows? The answer is still one.

In the case of multiple affected rows, both INSERTED and DELETED tables will contain multiple rows.

If you want your trigger to report all affected rows, you need to write a loop

select * from ggl_users;

-- reporting the first affected row only

UPDATE ggl_users SET email=name;

GO

Email changed from NULL to John King

(5 row(s) affected)

-- reporting all affected rows

ALTER TRIGGER update_user ON ggl_users

AFTER UPDATE

AS

SELECT 'Email changed from '

+ ISNULL(d.email,'NULL')

+ ' to '

+ ISNULL(i.email,'NULL')

FROM INSERTED AS i, DELETED AS d

WHERE i.id = d.id;

GO

UPDATE ggl_users SET email=REVERSE(name);

GO

------------------------------------------------------

Email changed from Marc Kumar to ramuK craM

Email changed from Roy Bush to hsuB yoR

Email changed from Jack Gate to etaG kcaJ

Email changed from Nancy Greenberg to grebneerG ycnaN

Email changed from John King to gniK nhoJ

(5 row(s) affected)

Sometime, you may want to implement some business logics in a DML trigger to cancel the DML statement. For example, you may want to check the new email address format provided by the UPDATE statement. If the email address is invalid, you to cancel the UPDATE statement.

There is no easy way to cancel the DML statement in a DML trigger. But there is easy way to override the DML statement with an "INSTEAD OF" trigger. SQL Server supports 2 options (3 keywords) on when the defined trigger will be fired:

* AFTER - Trigger fired after the DML statement executed successfully.

* INSTEAD OF - Trigger fired instead of the DML statement execution, allowing the trigger to decide whether or not, and how, to execute the statement.

* FOR - Same as AFTER.

The tutorial exercise below shows you how define an "INSTEAD OF" trigger on ggl_users to validate email addresses:

CREATE TRIGGER check_email ON ggl_users

INSTEAD OF UPDATE

AS

DECLARE @count INT;

SELECT @count = COUNT(*) FROM INSERTED

WHERE email NOT LIKE '%_@_%';

IF @count = 0

UPDATE ggl_users SET email=i.email

FROM INSERTED AS i

WHERE ggl_users.id = i.id

ELSE

PRINT 'Invalid email(s) found.';

GO

-- invalid email

UPDATE ggl_users SET email='john.king'

WHERE name

A DDL trigger is defined to handle a DDL statement event, like create, alter and drop tables, views, indexes, etc. DDL triggers can be used to generate warning messages on database object changes. The format of creating a DDL trigger should be:

CREATE TRIGGER trigger_name ON DATABASE

AFTER ddl_event_types

AS

statements

GO

-- ddl_event_types are keywords like:

-- CREATE_TABLE, ALTER_TABLE, DROP_TABLE, ...

Below is a simple example of creating a DDL trigger to generate messages on ALTER_TABLE events:

USE GlobalGuideLineDatabase;

GO

CREATE TRIGGER ddl_message ON DATABASE

AFTER ALTER_TABLE

AS

PRINT 'Someone is changing tables!';

GO

ALTER TABLE ggl_users

ALTER COLUMN id INT NOT NULL;

GO

Someone is changing tables!

Can you roll back the DDL statement in a trigger? The answer is yes. Since the DDL statement that fires the trigger and the statements defined inside the trigger are all executed as a single statement batch, you can add a ROLLBACK statement in the trigger to rollback the entire batch.

USE GlobalGuideLineDatabase; GO CREATE TRIGGER drop_rollback ON DATABASE AFTER DROP_TABLE AS PRINT 'Drop table is not allowed!'; ROLLBACK; GO DROP TABLE ggl_users; GO Drop table is not allowed! Msg 3609, Level 16, State 2, Line 2 The transaction ended in the trigger. The batch has been aborted.

This trigger is powerful. It will stop you from dropping any tables in GlobalGuideLineDatabase database.

A cursor is a special data type that represents a result set returned by a SELECT query statement. There are several notes about cursor you need to remember:

* Cursor data type can not be used to define table columns.

* Cursor data type is used on store procedures, functions, and triggers to help you loop through result sets returned by queries.

* Cursor data type can be used to define cursor variables.

* There are special Transact-SQL statements dedicated to work with cursors variables: OPEN, FETCH, CLOSE, and DEALLOCATE.

* Cursor variables can be passed as procedure or function parameters.

* There is a special function, CURSOR_STATUS(), for check cursor statuses.

If you want to use a cursor to represent the result set of a query, you need to define a cursor name with a SELECT sub-statement using the "DECLARE ... CURSOR" statement using the following syntax format:

DECLARE cursor_name CURSOR FOR

SELECT ...;

Note that the DECLARE statement will not actually execute the SELECT sub-statement. It only attaches the SELECT sub-statement to the cursor.

A cursor name should be deallocated to free up server resources if the cursor is not needed any more.

The tutorial example below shows you how to declare and deallocate a cursor.

USE GlobalGuideLineDatabase;

GO

DECLARE ggl_cursor CURSOR FOR

SELECT * FROM ggl_links;

-- other statements

DEALLOCATE ggl_cursor;

GO

When the result set is ready in a cursor, you can use a FETCH statement to retrieve one row from the result set in the same format as a SELECT statement. The FETCH statement has the following formats:

FETCH NEXT FROM cursor_name;

FETCH PRIOR FROM cursor_name;

FETCH FIRST FROM cursor_name;

FETCH LAST FROM cursor_name;

FETCH ABSOLUTE n FROM cursor_name;

FETCH RELATIVE n FROM cursor_name;

The tutorial exercise below shows you how FETCH statements are used to retrieve the first row and the second row back from a cursor:

USE GlobalGuideLineDatabase;

GO

DECLARE ggl_cursor CURSOR FOR

SELECT * FROM ggl_links;

OPEN ggl_cursor;

FETCH NEXT FROM ggl_cursor;

FETCH NEXT FROM ggl_cursor;

CLOSE ggl_cursor;

DEALLOCATE ggl_cursor;

GO

id url    notes  counts time

---- ------------------- ----------- ------- -----

101 rendc.org NULL  NULL NULL

(1 row(s) affected)

id url    notes  counts time

---- ----------------------- ----------- ------- -----

102 rendc.org/sql Nice site. 8  NULL

(1 row(s) affected)

Once a cursor is declared, you need to execute the query attached to the cursor so that the result set returned from the query can be accessed through the cursor. To execute the cursor query, you should use the OPEN statement as in this format:

OPEN cursor_name;

When you are done with using the result set attached to a cursor, you should close the result set to free up server resources.

The tutorial example below shows you how to open and close a cursor:

USE GlobalGuideLineDatabase;

GO

DECLARE ggl_cursor CURSOR FOR

SELECT * FROM ggl_links;

OPEN ggl_cursor;

-- result set is ready to use

-- other statements

CLOSE ggl_cursor;

DEALLOCATE ggl_cursor;

GO

There are two ways to representing a cursor:

1. A cursor name - A static name representing a cursor object. A cursor name should be linked to a cursor object in the DECLARE statement.

2. A cursor variable name - A variable name pointing to a cursor object. A cursor variable name should be declared with the CURSOR data type. It should be then assigned with a cursor object using the SET statement.

The tutorial exercise below shows you how to declare a cursor variable and assign a cursor object to it:

USE GlobalGuideLineDatabase;

GO

-- declare a cursor variable

DECLARE @ggl_cursor CURSOR;

-- assign a cursor object

SET @ggl_cursor = CURSOR FOR

SELECT id, url, notes, counts, time FROM ggl_links;

By default, a FETCH statement will display the fetched row on the client program window. If you want to transfer the output data to variables, you can specify an INTO clause with a list of variables that matches the list of fields in the result set.

The tutorial exercise below shows you a good example of using the FETCH statement to transfer one row of output data from the result set to variables:

USE GlobalGuideLineDatabase;

GO

DECLARE ggl_cursor CURSOR FOR

SELECT id, url, notes, counts, time FROM ggl_links;

OPEN ggl_cursor;

DECLARE @id INT, @url VARCHAR(80), @notes VARCHAR(80),

@counts INT, @time DATETIME;

FETCH NEXT FROM ggl_cursor INTO @id, @url, @notes,

@counts, @time;

PRINT 'id = '+CONVERT(VARCHAR(20),ISNULL(@id,0));

PRINT 'url = '+ISNULL(@url,'NULL');

PRINT 'notes = '+ISNULL(@notes,'NULL');

PRINT 'counts = '+CONVERT(VARCHAR(20),ISNULL(@counts,0));

PRINT 'time = '+CONVERT(VARCHAR(20),ISNULL(@time,'2007'));

CLOSE ggl_cursor;

DEALLOCATE ggl_cursor;

GO

id = 101

url = rendc.org

notes = NULL

counts = 0

time = Jan 1 2007 12:00AM

If the underlying table is changed after the cursor is opened, should the changes be reflected in the cursor result set? The answer is based on the update option used when creating the cursor. SQL SERVER supports two update options:

1. STATIC - The result set will be a static copy created when the OPEN statement is executed. Subsequent updates on the underlying tables will not affect the result set. STATIC is the default option.

2. SCROLL - The result set will be dynamically updated each time when a FETCH statement is executed. In another word, the result set always reflects the latest changes on the underlying tables.

The tutorial script below gives you a good example of how dynamic cursors work:

USE GlobalGuideLineDatabase;

GO

DECLARE @ggl_cursor CURSOR;

SET @ggl_cursor = CURSOR FOR

SELECT id, url, notes, counts, time FROM ggl_links;

OPEN @ggl_cursor;

DECLARE @id INT, @url VARCHAR(80), @notes VARCHAR(80),

@counts INT, @time DATETIME;

FETCH NEXT FROM @ggl_cursor INTO @id, @url, @notes,

@counts, @time;

SQL Server offers two scrolling option on cursors:

1. FORWARD_ONLY - The cursor can only be scrolled forward with "FETCH NEXT" statements. In another word, you can only loop through the cursor from the first row to the last row. FORWARD_ONLY is the default option.

2. SCROLL - The cursor can be scrolled back and forth with "FETCH NEXT", "FETCH PRIOR", and other fetch options.

The tutorial example below creates a cursor with the SCROLL option so that the result set can be looped through backward:

DECLARE @ggl_cursor CURSOR;

SET @ggl_cursor = CURSOR SCROLL FOR

SELECT id, url, notes, counts, time

FROM ggl_links ORDER BY id;

OPEN @ggl_cursor;

DECLARE @id INT, @url VARCHAR(80), @notes VARCHAR(80),

@counts INT, @time DATETIME;

FETCH LAST FROM @ggl_cursor INTO @id, @url, @notes,

@counts, @time;

WHILE @@FETCH_STATUS = 0 BEGIN

PRINT CONVERT(CHAR(5),ISNULL(@id,0))

+CONVERT(CHAR(18),ISNULL(@url,'NULL'))

+CONVERT(CHAR(20),ISNULL(@notes,'NULL'))

+CONVERT(CHAR(4),ISNULL(@counts,0))

+CONVERT(CHAR(11),ISNULL(@time,'2007'));

FETCH PRIOR FROM @ggl_cursor INTO @id, @url, @notes,

@counts, @time;

END

CLOSE @ggl_cursor;

A NULL value is a special value that represents an unknown value. SQL Server supports NULL values with the following features:

* All data types used for table columns support NULL values. In another word, NULL values can be stored in database tables.

* Individual table columns may be defined to not allow NULL values. In this case, you can not assign NULL values to those columns.

* "NULL" is a keyword that represent a NULL value in expressions.

* NULL values can be used directly in SET (assignment) statements.

* If NULL values are involved in an arithmetic operation, the result will be a numeric NULL.

* If NULL values are involved in a string operation, the result will be a string NULL.

* If NULL values are involved in a datetime operation, the result will be a datetime NULL.

* If NULL values are involved in a bitwise operation, the result will be a binary NULL.

* If NULL values are involved in a comparison operation, the result will be a Boolean NULL.

* If NULL values are involved in a Boolean operation, the result could be TRUE, FALSE, or NULL.

* To test NULL values, you need to use two special operators, IS NULL and IS NOT NULL.

* Special functions are available to handle NULL values, like ISNULL(), and NULLIF()

The rule for assigning NULL values to variables or table columns is simple: Use keyword "NULL" directly as normal values. Specifically,

* "NULL" can be used in SET statements to assign NULL values to variables.

* "NULL" can be used in SET clauses in UPDATE statements.

* "NULL" can be used in value lists in INSERT statements.

* "NULL" can be used in parameter lists when calling stored procedures or functions.

The tutorial script below gives you some good examples:

USE GlobalGuideLineDatabase;

GO

-- assign NULL values to variables

DECLARE @birth_date DATETIME;

SET @birth_date = NULL;

SELECT @birth_date;

GO

-----------------------

NULL

-- assign NULL values to columns

UPDATE ggl_links SET notes = NULL;

GO

(8 row(s) affected)

-- assign NULL values to parameters

EXEC sp_help NULL;

GO

Name

----------------

ggl_links_dump

ggl_links_top

ggl_links_view

...