Trigger

[b:ae645d24dc]try this dummie[/b:ae645d24dc]

:twisted:

USE pubs

IF EXISTS (SELECT name FROM sysobjects

WHERE name = ´reminder´ AND type = ´TR´)

DROP TRIGGER reminder

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