Create Trigger to update insert to table specific values only

Question

I need to create trigger prevent insert and update to table employee under age 21 and over age 67 what next on the code?

CREATE TRIGGER allowInsertUpdateemployee ON dbo.employee
    AFTER UPDATE, INSERT 

AS
BEGIN
DECLARE @v_ageLow INT = 21,
        @v_ageHigh  INT = 67,
        @v_dateLow date,
        @v_dateHigh date

SET @v_dateLow = DATEADD(YEAR, -1 * @v_ageLow, GETDATE())
SET @v_dateHigh = DATEADD(YEAR, -1 * @v_ageHigh, GETDATE())



END

Are you sure you are getting correct values in `@_dateLow` and `@dateHigh` ? — Suraj Singh, Oct 03 '13 at 11:45

score 2 · Answer 1 · answered Oct 03 '13 at 11:19

2

Since the upper and lower bounds are fixed, a check constraint might be a more appropriate solution than a trigger

ALTER TABLE employee ADD CONSTRAINT ck_employee_age CHECK 
(DateOfBirth BETWEEN DATEADD(YEAR,-67,GETDATE()) AND DATEADD(YEAR,-21,GETDATE()))

answered Oct 03 '13 at 11:19

Ed Harper

21,127
4
54
80

Sorry. I need to do that by using Trigger – isl65 Oct 03 '13 at 11:20

score 1 · Answer 2 · edited May 23 '17 at 12:03

1

Use "INSTEAD OF INSERT, UPDATE" trigger;
Use INSERTED table to check new incoming values, raiserror if needed;
Use DELETED table to detect if update is processing (this can help);
Do manual insert or update then (if needed).

INSERT INTO dbo.employee SELECT * FROM INSERTED I

edited May 23 '17 at 12:03

Community

1
1

answered Oct 03 '13 at 11:30

SalientBrain

2,431
16
18

Why an AFTER trigger wouldn't do? – Andriy M Oct 04 '13 at 05:24
@AndriyM Which one will be more efficient INSTEAD or AFTER in this situation ? – Suraj Singh Oct 04 '13 at 07:28
@SurajSingh: It is not about efficiency but about complexity and maintainability. When the rows being inserted violate rules that the trigger is implementing, both an AFTER trigger and an INSTEAD OF trigger would probably do more or less the same things: raise an exception, probably roll back the transaction too, i.e. they are equal in that. But if the rows are fine, an INSTEAD OF trigger would have to perform the actual operation (whether INSERT or UPDATE), while an AFTER trigger wouldn't need to do anything. Sometimes an INSTEAD OF trigger is unavoidable, but not in this case, in my opinion. – Andriy M Oct 04 '13 at 08:32

Suraj Singh · Answer 3 · 2013-10-04T07:18:05.003

0

I hope this will do
This will not fetch accurate age , However if your method gets you an accurate date you can use your code with it .

OR

you can also use below code to get age:

SELECT DATEDIFF(Day,'2011-11-03' , GETDATE()) /365

SELECT DATEDIFF(Day,@Age , GETDATE()) /365

 CREATE TRIGGER allowInsertUpdateemployee ON dbo.employee
      INSTEAD OF Insert 

    AS
    BEGIN
    DECLARE @v_ageLow INT,
            @v_ageHigh  INT,
            @v_date date,
          --@v_dateHigh date

    SET @v_ageLow  = SELECT DATEDIFF(Year,@v_date  , GETDATE())
    SET @v_ageHigh = SELECT DATEDIFF(Year,@v_date  , GETDATE())
    BEGIN
            if(@v_ageLow <21  AND @v_ageHigh >67)
            BEGIN
                RAISERROR('Cannot Insert or Update where User is not in age limit);
                ROLLBACK;
            END

            ELSE

                 BEGIN 

             // else write your insert update query
                Insert into values ()
                PRINT 'Unable to Insert-- Instead Of Trigger.'
            END    

    END

edited Oct 04 '13 at 07:18

answered Oct 03 '13 at 11:56

Suraj Singh

4,041
1
21
36

This doesn't make much sense so far. What values do `@v_dateLow` and `@v_dateHigh` have at the time of calling the DATEDIFFs? And you aren't reading the inserted rows at all when testing the age – *what* are you checking then? – Andriy M Oct 04 '13 at 05:28
@AndriyM Will it be beneficial using `INSTEAD OF` here ? – Suraj Singh Oct 04 '13 at 07:19
I can't see any benefits but that was not my point. Your trigger isn't checking *rows*, it is checking only *variables*. You were probably too focused on the calculations and overlooked that fact. – Andriy M Oct 04 '13 at 08:37

score 0 · Answer 4 · edited Aug 25 '17 at 09:07

You have to put OR in the where clause. The employee can't be under 21 AND more than 67.

Create TRIGGER tr_Too_young_or_too_old 
ON TableName
AFTER INSERT
AS
if exists ( select *, DATEDIFF(yy, birthdate, GETDATE()) as age 
            from TableName
where DATEDIFF(yy, birthdate, GETDATE()) < 21 or DATEDIFF(yy, birthdate, GETDATE()) > 67 )
begin
    rollback
    RAISERROR ('Cannot Insert or Update where User is not in age limit', 16, 1);
end

Create Trigger to update insert to table specific values only

4 Answers4