1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to write after insert event update trigger on the same table in ms sql 2005

Discussion in 'Other Databases' started by kanika bhagat, Oct 11, 2010.

  1. hi,
    I have one table student and three fields mtt1,mtt2 and sum.I want to update sum field whenever there is a new value inserted in mtt1 and mtt2.I want to use trigger and nothing else for this
    defination for the table student is given below:
    create table "student"
    (
    "rollno" varchar2(4000),
    "class" varchar2(4000),
    "sem" varchar2(4000),
    "sub" varchar2(4000),
    "mtt1" number,
    "mtt2" number,
    "external" number,
    "tot" number,
    )
    output will be like
    if we insert values in all the fields such as
    rollno - 08ejccs034
    class -1csa
    sem-1st
    sub-maths
    mtt1-7
    mtt2-8
    external-50
    then total will be generated automatically like
    tot-65
    and I want to do this
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I don't know of any reliable relational database that will allow a table to be updated at the same time as it's being inserted into from the same transactional statement yet this is what you want to do. It's called a mutating table. I expect SQL Server won't let you do that; I know for a fact Oracle won't.
     
  3. M_Anas_O

    M_Anas_O Forum Advisor

    Messages:
    95
    Likes Received:
    9
    Trophy Points:
    160
    Location:
    Austria
    May be you can think of a workaround for your requirement..
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Try this in trigger, this code surely works in oracle, but i am not sure will this works in ms sql 2005. Here i am giving the code as per oracle syntax. so modify according to ms sql 2005. try and let me know.

    create trigger cal_trig
    before insert
    on student
    begin
    If inserting then
    :new.tot := :new.mtt1 + :new.mtt2;
    end if;
    end cal_trig;
     
    Sadik likes this.
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It doesn't work in Oracle as NULL values for mtt1 and mtt2 are not dealt with at all, and the table specification does not declare these two columns as NOT NULL. Your sum will be NULL should either mtt1 or mtt2 be NULL, which is not the deisred result. Run the following example to prove this and see how to properly handle such situations in a trigger:

    Code (SQL):
     
    CREATE TABLE student
    (
    rollno varchar2(4000),
    class varchar2(4000),
    sem varchar2(4000),
    sub varchar2(4000),
    mtt1 NUMBER,
    mtt2 NUMBER,
    external NUMBER,
    tot NUMBER
    );
     
    CREATE OR REPLACE TRIGGER cal_trig
    BEFORE INSERT
    ON student
    BEGIN
    IF inserting THEN
    :NEW.tot := :NEW.mtt1 + :NEW.mtt2;
    END IF;
    END cal_trig;
    /
     
    INSERT INTO student
    (rollno,
    class,
    sem,
    sub,
    mtt1,
    mtt2,
    external)
    VALUES
    ('08ejccs034',
     '1csa',
     '1st',
     'maths',
     7,
     8,
     50);
     
    SELECT * FROM student;
     
    INSERT INTO student
    (rollno,
    class,
    sem,
    sub,
    mtt1,
    mtt2,
    external)
    VALUES
    ('09ejccs067',
     '1csa',
     '1st',
     'maths',
     NULL,
     8,
     45);
     
    SELECT * FROM student;
     
    DELETE FROM student WHERE rollno = '09ejccs067';
     
    CREATE OR REPLACE TRIGGER cal_trig
    BEFORE INSERT
    ON student
    BEGIN
    IF inserting THEN
    :NEW.tot := nvl(:NEW.mtt1,0) + nvl(:NEW.mtt2,0);
    END IF;
    END cal_trig;
    /
     
    INSERT INTO student
    (rollno,
    class,
    sem,
    sub,
    mtt1,
    mtt2,
    external)
    VALUES
    ('09ejccs067',
     '1csa',
     '1st',
     'maths',
     NULL,
     8,
     45);
     
    SELECT * FROM student;
     
     
  6. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi bhagat,

    If possible you can use virtual column as tot for your table and you can write in below manner to generate total.

    Code (Text):
    create or replace function fun_total(mtt1 number,mtt2 number,external number) return number deterministic as
    begin
    return (mtt1+mtt2+external);
    end fun_total;
    /
     
    Code (Text):
    create table "student"
    (
    "rollno" varchar2(4000),
    "class" varchar2(4000),
    "sem" varchar2(4000),
    "sub" varchar2(4000),
    "mtt1" number,
    "mtt2" number,
    "external" number,
    "tot" as (fun_total(mtt1,mtt2,external)),
    );
     
    Sadik likes this.
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your example, after several moldifications to finally get it to work, will require quotation marks around the table name and column names to select data from that table:

    Code (SQL):
    SQL> CREATE OR REPLACE FUNCTION fun_total(p_val1 NUMBER, p_val2 NUMBER, p_val3 NUMBER) RETURN NUMBER deterministic AS
    2 BEGIN
    3 RETURN (p_val1+p_val2+p_val3);
    4 END fun_total;
    5 /
     
    FUNCTION created.
     
    SQL>
    SQL> CREATE TABLE "student"
    2 (
    3 "rollno" varchar2(4000),
    4 "class" varchar2(4000),
    5 "sem" varchar2(4000),
    6 "sub" varchar2(4000),
    7 "mtt1" NUMBER,
    8 "mtt2" NUMBER,
    9 "extnum" NUMBER,
    10 "tot" AS (fun_total("mtt1","mtt2","extnum"))
    11 );
     
    TABLE created.
     
    SQL>
    SQL> SELECT * FROM student;
    SELECT * FROM student
    *
    ERROR at line 1:
    ORA-00942: TABLE OR VIEW does NOT exist
     
    SQL>
     
    It is not necessary to wrap column and table names in quotation marks if you don't use unusual characters:

    Code (SQL):
    SQL> CREATE OR REPLACE FUNCTION fun_total(p_val1 NUMBER, p_val2 NUMBER, p_val3 NUMBER) RETURN NUMBER deterministic AS
    2 BEGIN
    3 RETURN (p_val1+p_val2+p_val3);
    4 END fun_total;
    5 /
     
    FUNCTION created.
     
    SQL>
    SQL> CREATE TABLE student
    2 (
    3 rollno varchar2(4000),
    4 class varchar2(4000),
    5 sem varchar2(4000),
    6 sub varchar2(4000),
    7 mtt1 NUMBER,
    8 mtt2 NUMBER,
    9 extnum NUMBER,
    10 tot AS (fun_total(mtt1,mtt2,extnum))
    11 );
     
    TABLE created.
     
    SQL>
    SQL> SELECT * FROM student;
     
    no ROWS selected
     
    SQL>
    SQL> SELECT TABLE_NAME FROM user_tables;
     
    TABLE_NAME
    ------------------------------
    student
    STUDENT
     
    SQL>
     
    Notice both tables exist but only the one created without quotation marks around the name is directly avaiable without using the quotes. Also notice that the function call in your example requires the use of quotation marks to successfully create the table. [Variables passed to a function or procedure should be named to show what they pass, not named for columns in a specific table, and should be prefixed with p_ to indicate they are passed parameters.]

    Now that we have a usable table let's populate the table with values (we recreate the table with more realistic column definitions):

    Code (SQL):
    SQL>          ROLLNO            MTT1            MTT2          EXTNUM             TOT
    --------------- --------------- --------------- --------------- ---------------
                  1               1               1               1               3
                  2               2               2               2               6
                  3               3               3               3               9
                  4               4               4               4              12
                  5               5               5               5              15
                  6               6               6               6              18
                  7               7               7               7              21
                  8               8               8               8              24
                  9               9               9               9              27
                 10              10              10              10              30

    10 ROWS selected.
     
    Your suggestion does work; it would have been good to have tested your example before posting it as it doesn't run as you originally wrote it.
     
  8. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    sorry for that david,

    I have just posted sample code there but I haven't given any trail on that. Really sorry about that. The virtual columns concept works fine for his requirement. He asked that when we are trying to insert a record then he wants to update the field with total of other fields which are entered. So for that this virtual column concept works fine I think.

    Code (Text):


    1. To create function for getting total
     
       create or replace function fun_total(p_val1 number, p_val2 number, p_val3 number) return number deterministic as
       begin
       return (p_val1+p_val2+p_val3);
       end fun_total;
       
    2. To create table.

    create table "student"
       (
       rollno varchar2(4000),
       class varchar2(4000),
       sem varchar2(4000),
       sub varchar2(4000),
       mtt1 number,
       mtt2 number,
       extnum number,
       tot as (fun_total(mtt1,mtt2,extnum))
       );

    3. To insert into table.

    insert into "student"(ROLLNO,CLASS,SEM,SUB,MTT1,MTT2,EXTNUM) VALUES('A1','AA','A11','ENG',50,60,24);

    4. Check whether record is inserted and total is generated as required.

    SELECT * FROM "student";

    Output: - Here total is generated automatically.

    ROLLNO  CLASS   SEM SUB MTT1    MTT2    EXTNUM  TOT
    A1  AA  A11 ENG 50  60  24  134


    5. This is how we can delete the table

    drop table "student";

    or we can remove quotations in all statements and we can work on it. The above code works fine now. Apologies for not testing code before
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It is so much better to test code before posting; there are many here new to Oracle and posting untested code can lead these members to believe they did somethiing wrong when it doesn't work as it is supposed to. Knowing your examples work makes life easier on all members; Oracle is difficult enough to learn and having untested example code can only lead to confusion and frustration. We certainly don't want those learning about Oracle to question their own knowledge by putting unintentional roadblocks in their way.

    Your intentions were good and honorable, however it is best to test such examples so others can learn from them and know they work as expected. With knowledge comes power and responsibility -- the power is in the ability to teach and the responsibility is in verifying your teaching material so others can benefit.
     
    Bharat likes this.
  10. kumar253

    kumar253 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Create a table employee(emp_id, emp_name, dob, doj)...

    While inserting a record in employee table, trigger is called automatically,

    In Trigger, you have to check whether the emp_id is available or not, If available raise a user-defined exception 'Emp is already exist'
    or other wise insert record in table.
     
  11. kumar253

    kumar253 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    plz give reply
     
  12. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    kumar253 wrote

    "Create a table employee(emp_id, emp_name, dob, doj)...

    While inserting a record in employee table, trigger is called automatically,

    In Trigger, you have to check whether the emp_id is available or not, If available raise a user-defined exception 'Emp is already exist'
    or other wise insert record in table. "

    That is not a scalable or usable option; you do not want to perform key verification with transactional code as the current transaction cannot 'see' any concurrent transactions from other users until a 'commit' is issued:

    Code (SQL):
    SQL> CREATE TABLE employee(
    2 emp_id NUMBER,
    3 emp_name varchar2(40),
    4 dob DATE,
    5 hiredate DATE,
    6 salary NUMBER
    7 );
     
    TABLE created.
     
    SQL>
    SQL>
    SQL> CREATE TRIGGER upd_empl_trg
    2 BEFORE UPDATE ON employee
    3 FOR each ROW
    4 DECLARE
    5 v_empid NUMBER;
    6 BEGIN
    7 SELECT emp_id INTO v_empid
    8 FROM employee
    9 WHERE emp_id = :NEW.emp_id;
    10
    11 IF v_empid IS NOT NULL THEN
    12 raise_application_error(-20998,'Employee '||:NEW.emp_id||' exists.');
    13 END IF;
    14 END;
    15 /
     
    TRIGGER created.
     
    SQL>
    SQL> BEGIN
    2 FOR i IN 1..1000 loop
    3 INSERT INTO employee
    4 VALUES(i, 'Snarpo'||i, sysdate-i, sysdate+i, MOD(i,13)*100);
    5 END loop;
    6
    7 commit;
    8
    9 END;
    10 /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> INSERT INTO employee
    2 VALUES (1001, 'Sping Yarpo', sysdate-13, sysdate+12, 1345);
     
    1 ROW created.
     
    SQL>
    SQL> pause
     
     
    Session #2:

    Code (SQL):
    SQL> INSERT INTO employee
    2 VALUES (1001, 'Sping Yarpo', sysdate-13, sysdate+12, 1345);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    Notice that even thought there IS an insert pending a commit that inserted the same record as session #2 the insert succeeded. Back to session #1:

    Code (SQL):
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT * FROM employee WHERE emp_id = 1001;
     
    EMP_ID EMP_NAME DOB HIREDATE
    ---------- ---------------------------------------- --------- ---------
    SALARY
    ----------
    1001 Sping Yarpo 14-AUG-12 08-SEP-12
    1345
    1001 Sping Yarpo 14-AUG-12 08-SEP-12
    1345
     
    SQL>
    And there are now two records in the table with the same emp_id because the trigger could NOT see the pending insert. To actually prevent duplicate records a primary key needs to be created:

    Code (SQL):
    SQL> CREATE TABLE employee(
    2 emp_id NUMBER,
    3 emp_name varchar2(40),
    4 dob DATE,
    5 hiredate DATE,
    6 salary NUMBER
    7 );
     
    TABLE created.
     
    SQL>
    SQL> ALTER TABLE employee ADD CONSTRAINT employee_pk PRIMARY KEY(emp_id);
     
    TABLE altered.
     
    SQL>
    SQL> BEGIN
    2 FOR i IN 1..1000 loop
    3 INSERT INTO employee
    4 VALUES(i, 'Snarpo'||i, sysdate-i, sysdate+i, MOD(i,13)*100);
    5 END loop;
    6
    7 commit;
    8
    9 END;
    10 /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> INSERT INTO employee
    2 VALUES (1001, 'Sping Yarpo', sysdate-13, sysdate+12, 1345);
     
    1 ROW created.
     
    SQL>
    SQL> pause
    SQL>
    From session #2:

    Code (SQL):
    SQL> INSERT INTO employee
    2 VALUES (1001, 'Sping Yarpo', sysdate-13, sysdate+12, 1345);
    INSERT INTO employee
    *
    ERROR at line 1:
    ORA-00001: UNIQUE CONSTRAINT (BING.EMPLOYEE_PK) violated
     
    SQL>
    The transaction in session #2 waited until the commit from session #1 before returning the error message shown; the constraint prevented the insert from occurring in session #2 until it was known what was inserted from session #1 -- something the trigger cannot do.

    Data integrity cannot be reliably enforced with a trigger, ever.

    The output from session #1, showing only one record with the new emp_id:

    Code (SQL):
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT * FROM employee WHERE emp_id = 1001;
    EMP_ID EMP_NAME DOB HIREDATE
    ---------- ---------------------------------------- --------- ---------
    SALARY
    ----------
    1001 Sping Yarpo 14-AUG-12 08-SEP-12
    1345
     
    SQL>