use pubs
go


print 'drop'
go
-- so message can be added
sp_dropmessage @msgnum=50005

-- so obsoletejobs is empty for test
drop table obsoletejobs

/* uncomment this if you want to use manual deployment
drop procedure getAuthorsByState
drop procedure getAuthorInfo
drop procedure getAuthorMessage
drop procedure getAuthorWithErrors
drop trigger AddToObsolete
drop trigger AuthorCheckContract
*/

-- to clean out the jobs the might have
-- been added by a previous run
delete jobs where job_desc='newxyzzy'

-- to insure that contracts are 1
-- for the update trigger test
update authors set contract = 1
go

-- add custom message to be used
-- when an author cannot be found
sp_addmessage 50005, 16,
    'author with ssn: %s, not found in the database'
go

/* uncomment this if you want to use manual deployment
drop assembly Provider
go

print 'Create Provider'

create assembly Provider
from 'c:\types\Provider.dll'
go


print 'Create Procs'
go

create procedure getAuthorsByState(@state nchar(2))
as
external name Provider.[PubsProcs].getAuthorsByState
go
*/

exec getAuthorsByState 'CA'
go


/* uncomment this if you want to use manual deployment

print 'create procedure getAuthorInfo'
go

create procedure getAuthorInfo(@au_id nvarchar(11), @au_info nvarchar(4000) out)
as
external name Provider.[PubsProcs].getAuthorInfo
go
*/

print 'author by ssn'
go

declare @a nvarchar(4000)
exec getAuthorInfo '172-32-1176', @a out
if @a is null print 'NULL'
print @a
go
-- this author does not exist
-- check the output to see the result
-- notice that a generic message 
-- is output, which does not indicate
-- the real cause of the error
declare @a nvarchar(4000)
exec getAuthorInfo '111-11-1111', @a out
if @a is null print 'NULL'
print @a
go

/* uncomment this if you want to use manual deployment
print 'create procedure getAuthorMessage'
go

create procedure getAuthorMessage(@au_id nvarchar(11))
as
external name Provider.[PubsProcs].getAuthorMessage
go
*/

-- this works the same as previous
-- test, but uses SqlPipe to return data
print 'getAuthorMessage'
go
exec getAuthorMessage '172-32-1176'
go

/* uncomment this if you want to use manual deployment
print 'create procedure getAuthorWithErrors'
go

create procedure getAuthorWithErrors(@au_id nvarchar(11),  @au_info nvarchar(4000) out)
as
external name Provider.[PubsProcs].getAuthorWithErrors
go
*/

print 'author errors'
go

declare @a nvarchar(4000)
exec getAuthorWithErrors '172-32-1176', @a out
print @a
go
-- this author does not exist
-- this procedure checks for this
-- look at the output to see the results
-- notice that there is a custom error
-- message (way over on right)
declare @a nvarchar(4000)
print 'exec getAuthorWithErrors ''111-11-1111'', @a out'
exec getAuthorWithErrors '111-11-1111', @a out
print @a
go

/* uncomment this if you want to use manual deployment
print 'create TRIGGER AddToObsolete'
go

create TRIGGER AddToObsolete
ON JOBS 
FOR DELETE
as
external name Provider.[PubsTriggers].AddToObsolete
go
*/

print 'select * from information_schema.columns'
go

-- to get column values
select * from information_schema.columns where table_name = 'JOBS'


print 'create table obsoletejobs'
go 

-- create table obsoletejobs here
create table obsoletejobs(
 job_id smallint,
 job_desc varchar(50),
 min_lvl tinyint,
 max_lvl tinyint)
go


print 'insert jobs values'
go
-- add a job to the jobs table
-- the existing jobs have references to
-- them and cannot be deleted, so 
-- a new one is needed to try
-- out the delete trigger
print 'insert jobs values(''newxyzzy'', 10, 10)'
-- give the job a description that can't possible
-- be in the jobs table already
insert jobs values('newxyzzy', 10, 10)
go
-- now try an delete the job
-- that was just added
delete jobs where job_desc='newxyzzy'
go

-- check the jobs table and make
-- sure that there is no newxyzzy 
-- job there
select * from jobs
-- check to make sure that trigger
-- worked. job newxyzzy should be in obsoletejobs
print 'select * from obsoletejobs'
select * from obsoletejobs
go

/* uncomment this if you want to use manual deployment
print 'create TRIGGER AuthorCheckContract'
go

create TRIGGER AuthorCheckContract
ON AUTHORS 
FOR UPDATE
as
external name Provider.[PubsTriggers].AuthorCheckContract
go
*/

print 'select * from authors where contract = 0'
go

select * from authors where contract = 0

select * from authors a
join titleauthor ta on a.au_id = ta.au_id
where ta.royaltyper > 30
print 'update authors'
update authors set contract = 0 where au_id = '213-46-8915'
go
select au_id, contract from authors
go
declare @contract tinyint
select @contract=contract from authors where au_id  = '213-46-8915'
print 'Contract ' + Convert(varchar(5), @contract)
go
