Testing Trigger

CREATE TRIGGER trig_addAuthor
ON authors
FOR INSERT

AS

-- Get the first and last name of new author
DECLARE @newName VARCHAR(100)
SELECT @newName = (SELECT au_fName + ' ' + au_lName FROM Inserted) //virtual table

-- Print the name of the new author
P RINT 'New author "' + @newName + '" added.'

-Initialize Query Analyzer
-Execute following code

USE pubs
SET NOCOUNT ON

INSERT INTO authors(au_id, au_lname, au_fname, phone, address, city, state, zip, contract)
VALUES('172-85-4534', 'Doe', 'John', '123456', '1 Black Street', 'Doeville', 'CA', '90210', 0)

Result
New author "John Doe" added.

AS

DECLARE @oldName VARCHAR(100)
DECLARE @newName VARCHAR(100)

IF NOT UPDATE(au_fName) AND NOT UPDATE(au_lName)

BEGIN
RETURN
END

SELECT @oldName = (SELECT au_fName + ' ' + au_lName FROM Deleted)
SELECT @newName = (SELECT au_fName + ' ' + au_lName FROM Inserted)

PRINT 'Name changed from "' + @oldName + '" to "' + @newName + '"'

SET au_lName = 'Black'

WHERE au_id = '172-32-1176'

Result
Name changed from "John Doe" to "John Black"

AS

DECLARE @isOnContract BIT
SELECT @isOnContract = (SELECT contract FROM Deleted)

IF(@isOnContract = 1)
BEGIN

PRINT "Code to notify publisher goes here"
END