December 10, 2003 at 7:39 am
Hi,
I am taking a SQL course at a community college. (SQL Server 2000) We have been assigned the task of writing a SP and a trigger. The specs for the SP are that it Declare input variables, take parameters in when procedure is called, work with a file that exist in the Pubs dbase, use programing control stucture (conditions, looping), length to be 30-40 lines, and demonstrate results.
Trigger requirements are Choose one action to fire the trigger, Make the trigger perform an action(s) on a table, Print a message to verify what's been done to the screen.
I must use the Pubs database. This is for a class presentation. Anyone have any suggestions where to start or ideas for a stored procedure or trigger? We haven't covered either in class so I am new to both of them...
Thanks!!
Mark
December 10, 2003 at 7:57 am
There are a lot of examples for the pubs database in BOL.
Like this for a stored procedure
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info_all' AND type = 'P')
DROP PROCEDURE au_info_all
GO
CREATE PROCEDURE au_info_all
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
GO
or this for a trigger
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'employee_insupd' AND type = 'TR')
DROP TRIGGER employee_insupd
GO
CREATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
/* Get the range of level for this job type from the jobs table. */
DECLARE @min_lvl tinyint,
@max_lvl tinyint,
@emp_lvl tinyint,
@job_id smallint
SELECT @min_lvl = min_lvl,
@max_lvl = max_lvl,
@emp_lvl = i.job_lvl,
@job_id = i.job_id
FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id
JOIN jobs j ON j.job_id = i.job_id
IF (@job_id = 1) and (@emp_lvl <> 10)
BEGIN
RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
ROLLBACK TRANSACTION
END
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
BEGIN
RAISERROR ('The level for job_id:%d should be between %d and %d.',
16, 1, @job_id, @min_lvl, @max_lvl)
ROLLBACK TRANSACTION
END
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply