none
TRIGGER when UPDATE is performed

    Question

  • Hi All,

    This might be a dumb question but, I've never used TRIGGERS so far. I want to create a trigger on a table when updates are performed.

    create table EMP (ENO NUMERIC(4) Not Null,
        ENAME VARCHAR(10) Not Null,
        SAL NUMERIC(7,2) NOT NULL,
        COMM NUMERIC(7,2),
        DNO NUMERIC(2),
        DNAME VARCHAR(20));


    create table EMP_Audit (ENO NUMERIC(4) Not Null,
    	ENAME VARCHAR(10) Not Null,
    	SAL NUMERIC(7,2) NOT NULL,
    	COMM NUMERIC(7,2),
    	DNO NUMERIC(2),
    	DNAME VARCHAR(20));


    insert into EMP (ENO,ENAME,SAL,DNAME) values (7369,'SMITH',1000,'ENGINEERING');
    insert into EMP (ENO,ENAME,SAL,DNAME) values (7499,'ALLEN',1600,'SALES');
    insert into EMP (ENO,ENAME,SAL,DNAME) values (7521,'WARD',1250,'SALES');
    insert into EMP (ENO,ENAME,SAL,DNAME) values (7654,'MARTIN',1250,'SALES');
    insert into EMP (ENO,ENAME,SAL,DNAME) values (7698,'BLAKE',2850,'SALES');
    
    

    When the salary of an employee is updated, a trigger compares the new salary and the old salary. The trigger only performs the update if the new salary is more than the old salary. Can you please help me with this?

    Thanks



    Known is a DROP, Unknown is an OCEAN.

    Monday, October 14, 2013 12:07 AM

Answers

  • When the salary of an employee is updated, a trigger compares the new salary and the old salary. The trigger only performs the update if the new salary is more than the old salary. Can you please help me with this?

    One method:

    CREATE TRIGGER dbo.TR_EMP_UPDATE ON dbo.EMP
    FOR UPDATE
    AS
    IF EXISTS(
    	SELECT *
    	FROM inserted AS i
    	JOIN deleted AS d ON
    		d.ENO = i.ENO
    	WHERE
    		i.SAL < d.SAL
    		)
    BEGIN
    	RAISERROR('new salary must be greater than old salary', 16, 1);
    	ROLLBACK;
    END;
    GO
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, October 14, 2013 1:36 AM

All replies

  • When the salary of an employee is updated, a trigger compares the new salary and the old salary. The trigger only performs the update if the new salary is more than the old salary. Can you please help me with this?

    One method:

    CREATE TRIGGER dbo.TR_EMP_UPDATE ON dbo.EMP
    FOR UPDATE
    AS
    IF EXISTS(
    	SELECT *
    	FROM inserted AS i
    	JOIN deleted AS d ON
    		d.ENO = i.ENO
    	WHERE
    		i.SAL < d.SAL
    		)
    BEGIN
    	RAISERROR('new salary must be greater than old salary', 16, 1);
    	ROLLBACK;
    END;
    GO
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, October 14, 2013 1:36 AM
  • Instead of going for a Trigger, why don't you add the check before the update statement?, that way you may have better control on the flow.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, October 14, 2013 3:51 AM
  • Why?  This is only for auditing and there is no "control" of flow desired - merely the recording of an increase.  An instead-of trigger would only be more complicated to write (trivially so for an experienced person but still accurate).
    Monday, October 14, 2013 2:00 PM
  • This is better off implementing in update procedure itself IMHO. There's no need of trigger here to check after modification and then doing rollback. Its simply a matter of checking and doing update only if rule is satisfied in procedure itself. I cant think of this as an auditing requirement either.
    Monday, October 14, 2013 2:05 PM
  • >> This might be a dumb question but, I’ve never used triggers so far. I want to create a trigger on a table when updates are performed. <<

    No, it is a common non-SQL, non_RDBMS mindset question from Noobs :) Your code is full of classic errors. I already dissected the design errors for this non-table, denormalized mess in another posting. Did you read it? 

    This mistake is at a higher level. We do not do audits inside the system under audit. It is illegal in finance, and not allowed by science and RDBMS. Think about what happens when there is a crash! This is like keeping all the back-up files in the same room as the computer, so they can all burn together. This is why the log file is on a physically separate drive or tape from the database. 

    There are several audit tools that comply with HIPPA, BASEL, GAAP, etc. standards and you should be using them. As a heuristic, you will write no more than 5 triggers in a career if you are competent. They do not port, they are procedural and a good programmer will use DRI Actions. Looking at your code you are 2-4 years away from being able to write SQL, but keep at it. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, October 14, 2013 8:11 PM