locked
Fire Insert or Update trigger after Insert RRS feed

  • Question

  • Hi

    I want to create a Trigger which execute Insert or Update statement to another table.

    In detail, when a new row create or delete in a table then a trigger file which

    1st check whether there have any data according to that ID. If not exist  then Insert that row to that table but if exist then update that row. This is to do inside a database in different table.

    Another question is,

    I want to do same from a database table to another database table.

    Please solve this for me.

    Thanks & Regards

    Thursday, August 28, 2014 11:13 AM

Answers

  • Yes I have seen your post but how can I apply this?

    Will it do automatic update and insert?

    Sorry If I do not understand. Could you please explain it for me.

    Thanks & Regards

    It will if you include the logic in your insert procedure.

    But if somebody still went ahead and do a manual insert it will not get captured

    To make sure it gets captured always you need a trigger on each of the tables tabl1,table2 and table3 which looks like this

    CREATE TRIGGER Trg_table1 ON table1 FOR INSERT AS BEGIN MERGE StoringTable AS d USING INSERTED AS s ON s.ID = d.ID WHEN MATCHED THEN UPDATE SET source1 = s.source1 WHEN NOT MATCHED THEN INSERT (id,source1) VALUES (s.id,s.source1);

    END


    I've given example for table1 so make sure you do corresponding changes for table2 and table3


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    Sunday, August 31, 2014 1:33 PM
  • Thanks Visakh.

    I got the clue. I will try to use it and let you know.

    But I think there is another problem will arise in future. I am trying to say, in future If any data need to be delete from table1 then the source table also need to delete the respected data. So I need it cascade relation. Now can you give advice or solution of this problem?

    Thanks again

    You can handle this in two ways

    1. If you enforce foreign key you can create it with ON DELETE CASCADE option so that referred entries will also get deleted automatically. This is easier to set up.

    2. Write a manual trigger to do the DELETE yourself. This will have more clarity as you yourself are implementing it. You may also choose to capture deleted record information in a audit table for tracking purpose.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Soulidentities Monday, September 1, 2014 11:58 AM
    Monday, September 1, 2014 8:06 AM
  • sorry for my poor explanation. :)

    you are right. that two field is primary key and same meaning but value is different. Since the value is different, I can not make the connection. But this two field also need to connected. otherwise I cant separate the unique row of those table. The visittype field contain three types of data. Initial, full, compliance. and the visit table contain 1,2 and 3.

    Now when I need compare in trigger, I also need s.visittype =d.visit. But
     since the containing value is not same thats why I cant do it. What I need is an If statement,

    such as If visittype='Initial' then var=1 , If visittype='Full' then var=2......

    So that I can compare this field also with visit.

    I dont know how to do it. Did I make it clear?

    Thanks for reply.

    ok for that you can create a derived column and join using it 

    ie like this

    ALTER TRIGGER [dbo].[ROSC10_Semister1]
    ON [dbo].[Student_ROSC10]
    FOR INSERT 
    AS 
    BEGIN
    
    MERGE Student AS d
    USING (SELECT DistrictID,upazilaID,LCID,EduYr,
    AdmClass,RollNo,EduYr,
    CASE VisitType
    WHEN 'Initial' THEN 1
    WHEN 'Full' THEN 2
    WHEN 'Compliance' THEN 3
    END AS Visit
    FROM INSERTED) AS s
    ON s.DistrictID = d.DistrictID and s.upazilaID= d.upazilaID and s.LCID=d.LCID and s.EduYr=d.StuYr and s.AdmClass=d.StuAdmClass AND s.Visit = d.Visit
    WHEN MATCHED THEN
    UPDATE 
    SET ROSC10_Status=1 
    WHEN NOT MATCHED THEN
    INSERT (DistrictID,upazilaID,LCID,RollNo,StuYr,StuAdmClass,ROSC10_Status)
    VALUES ( DistrictID, UpazilaID, LcID,RollNo,EduYr, AdmClass,1);
    END


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Soulidentities Wednesday, September 3, 2014 4:51 AM
    Tuesday, September 2, 2014 5:15 PM

All replies

  • Much better using an output clause, take a look at

    create table itest ( i int identity not null primary key, j int not null unique )
    create table #new ( i int not null, j int not null)

    insert into itest (j)
    output inserted.i, inserted.j into #new
    select o.object_id from sys.objects as o

    select * from #new
    drop table #new, itest;
    go

     
    Now from this example, you can see the integration of OUTPUT clause with existing DML syntax.
     
    Another common scenario is auditing of data in a table using triggers. In this case, the trigger uses information from the inserted and updated tables to add rows into the audit tables. The example below shows code that uses OUTPUT clause in UPDATE and DELETE statements to insert rows into an audit table.
     
    create table t ( i int not null );
    create table t_audit ( old_i int not null, new_i int null );
    insert into t (i) values( 1 );
    insert into t (i) values( 2 );
     
    update t
       set i  = i + 1
    output deleted.i, inserted.i into t_audit
     where i = 1;
     
    delete from t
    output deleted.i, NULL into t_audit
     where i = 2;
     
    select * from t;
    select * from t_audit;
     
    drop table t, t_audit;
    go

    Hope you have now got an understanding of the OUTPUT 
    clause in SQL Server 2005. It is a powerful feature that enables you
     to eliminate use of triggers in some cases or send results to client as 
    part of the data modification operation efficiently.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Naomi N Tuesday, September 2, 2014 5:23 PM
    Thursday, August 28, 2014 11:24 AM
    Answerer
  • Whats the purpose of delete here? What action do you want to do when a record is deleted from maintable?

    For the other part which is below

    1st check whether there have any data according to that ID. If not exist  then Insert that row to that table but if exist then update that row. This is to do inside a database in different table

    Looks like it can handled using a single MERGE statement to me


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Naomi N Tuesday, September 2, 2014 5:23 PM
    Thursday, August 28, 2014 11:45 AM
  • Why do you need a trigger?

    How about a stored procedure?


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012








    • Proposed as answer by Naomi N Tuesday, September 2, 2014 5:24 PM
    Thursday, August 28, 2014 11:47 AM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    >> I want to create a Trigger which execute Insert or Update statement to another table. <<

    Good SQL programmers want to avoid triggers and use DRI actions

    >> In detail, when a new row create or delete in a table then a trigger fires which first checks whether there have any data according to that ID (what “id”? We have no DDL!). If not exist then Insert that row to that (which?) table but if exist then update that row. <<

    MERGE Statement! Google 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

    Thursday, August 28, 2014 9:52 PM
  • Read this link and this will give you complete details how instead and after and more...

    http://technet.microsoft.com/en-us/library/aa258254%28v=sql.80%29.aspx

    http://msdn.microsoft.com/en-nz/library/ms177564.aspx

    http://msdn.microsoft.com/en-nz/library/ms189799.aspx


    Raju Rasagounder Sr MSSQL DBA

    Friday, August 29, 2014 2:53 AM
  • Actually I want it auto insert data to another table depending of the table where data first insert.

    That means when a insert statement occur in a table table, then it will automatic insert or update in another table.

    Table1

    ID    source1

    1      2

    2      2

    Table2

    ID    source2

    1      3

    3      Null

    Table3

    ID    source1

    1      Null

    2      4


    StoringTable

    id     source1    source2    source3

    1          2               3               Null

    1          2               Null            4

    data come in table 1,2,3 and will insert or update in storingTable. If no data in StoringTable then it will insert, otherwise update according to that ID. For this reason I need to check whether the StoringTable have that ID or not.

    I am trying to draw the scenario. Forgive me If I did any mistake.

    Thanks anyway.

    Sunday, August 31, 2014 10:10 AM
  • Have you seen my post above?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, August 31, 2014 11:06 AM
    Answerer
  • Yes I have seen your post but how can I apply this?

    Will it do automatic update and insert?

    Sorry If I do not understand. Could you please explain it for me.

    Thanks & Regards

    Sunday, August 31, 2014 11:47 AM
  • Yes I have seen your post but how can I apply this?

    Will it do automatic update and insert?

    Sorry If I do not understand. Could you please explain it for me.

    Thanks & Regards

    It will if you include the logic in your insert procedure.

    But if somebody still went ahead and do a manual insert it will not get captured

    To make sure it gets captured always you need a trigger on each of the tables tabl1,table2 and table3 which looks like this

    CREATE TRIGGER Trg_table1 ON table1 FOR INSERT AS BEGIN MERGE StoringTable AS d USING INSERTED AS s ON s.ID = d.ID WHEN MATCHED THEN UPDATE SET source1 = s.source1 WHEN NOT MATCHED THEN INSERT (id,source1) VALUES (s.id,s.source1);

    END


    I've given example for table1 so make sure you do corresponding changes for table2 and table3


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    Sunday, August 31, 2014 1:33 PM
  • Thanks Visakh.

    I got the clue. I will try to use it and let you know.

    But I think there is another problem will arise in future. I am trying to say, in future If any data need to be delete from table1 then the source table also need to delete the respected data. So I need it cascade relation. Now can you give advice or solution of this problem?

    Thanks again

    Monday, September 1, 2014 6:18 AM
  • Thanks Visakh.

    I got the clue. I will try to use it and let you know.

    But I think there is another problem will arise in future. I am trying to say, in future If any data need to be delete from table1 then the source table also need to delete the respected data. So I need it cascade relation. Now can you give advice or solution of this problem?

    Thanks again

    You can handle this in two ways

    1. If you enforce foreign key you can create it with ON DELETE CASCADE option so that referred entries will also get deleted automatically. This is easier to set up.

    2. Write a manual trigger to do the DELETE yourself. This will have more clarity as you yourself are implementing it. You may also choose to capture deleted record information in a audit table for tracking purpose.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Soulidentities Monday, September 1, 2014 11:58 AM
    Monday, September 1, 2014 8:06 AM
  • So I need to create delete trigger.

    okay I have already done the previous triggers successfully. :)

    Now I need to create delete trigger. Can I use Update trigger also?

    Thanks anyway. 

    Monday, September 1, 2014 11:58 AM
  • So I need to create delete trigger.

    okay I have already done the previous triggers successfully. :)

    Now I need to create delete trigger. Can I use Update trigger also?

    Thanks anyway. 

    Yes you can

    But whats the deal with UPDATE trigger? What do you want to capture in case of an UPDATE

    Actually if its same logic you can have a single trigger created for handling multiple actions ie DELETE & UPDATE for example


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, September 1, 2014 3:07 PM
  • Actually I have done the Insert trigger but when in I update a column in table1 then then I needed to update the source table also. That is why I am thinking about update trigger. 

    Also you are right that I can have all in a single trigger.

    So should I create all insert,update and delete in the same trigger?

    Tuesday, September 2, 2014 4:00 AM
  • I am having another problem which is related to the query.

    ALTER TRIGGER [dbo].[ROSC10_Semister1]
    ON [dbo].[Student_ROSC10]
    FOR INSERT 
    AS 
    BEGIN
    
    MERGE Student AS d
    USING INSERTED AS s
    ON s.DistrictID = d.DistrictID and s.upazilaID= d.upazilaID and s.LCID=d.LCID and s.StuYr=d.StuYr and s.AdmClass=d.StuAdmClass
    WHEN MATCHED THEN
    UPDATE 
    SET ROSC10_Status=1 
    WHEN NOT MATCHED THEN
    INSERT (DistrictID,upazilaID,LCID,RollNo,StuYr,StuAdmClass,ROSC10_Status)
    VALUES ( DistrictID, UpazilaID, LcID,RollNo,EduYr, AdmClass,1);
    END

    In the target column there have another column which does not have the same data for check. I mean the source field is Visittype and target field is visit.

    Now the Visittype contain only three date:

    1. Initital (Represent 1 in visit field)

    2. Full (Represent 2 in visit field)

    3. Compliance (Represent 3 in visit field)

    Now problem is I can not check this to field in the statement. which should be s.Visittype=d.visit.

    USING INSERTED AS s
    ON s.DistrictID = d.DistrictID and s.upazilaID= d.upazilaID and s.LCID=d.LCID and s.StuYr=d.StuYr and s.AdmClass=d.StuAdmClass

    What more should I do here.

    Please share advice .

    Thanks & Regards

    Tuesday, September 2, 2014 7:54 AM
  • I am having another problem which is related to the query.

    ALTER TRIGGER [dbo].[ROSC10_Semister1]
    ON [dbo].[Student_ROSC10]
    FOR INSERT 
    AS 
    BEGIN
    
    MERGE Student AS d
    USING INSERTED AS s
    ON s.DistrictID = d.DistrictID and s.upazilaID= d.upazilaID and s.LCID=d.LCID and s.StuYr=d.StuYr and s.AdmClass=d.StuAdmClass
    WHEN MATCHED THEN
    UPDATE 
    SET ROSC10_Status=1 
    WHEN NOT MATCHED THEN
    INSERT (DistrictID,upazilaID,LCID,RollNo,StuYr,StuAdmClass,ROSC10_Status)
    VALUES ( DistrictID, UpazilaID, LcID,RollNo,EduYr, AdmClass,1);
    END

    In the target column there have another column which does not have the same data for check. I mean the source field is Visittype and target field is visit.

    Now the Visittype contain only three date:

    1. Initital (Represent 1 in visit field)

    2. Full (Represent 2 in visit field)

    3. Compliance (Represent 3 in visit field)

    Now problem is I can not check this to field in the statement. which should be s.Visittype=d.visit.

    USING INSERTED AS s
    ON s.DistrictID = d.DistrictID and s.upazilaID= d.upazilaID and s.LCID=d.LCID and s.StuYr=d.StuYr and s.AdmClass=d.StuAdmClass

    What more should I do here.

    Please share advice .

    Thanks & Regards

    Sorry didnt understand why you cant compare

    do you mean values in field doesnt match between source and destination?

    Can you show some sample data and explain what issue is?


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, September 2, 2014 10:15 AM
  • sorry for my poor explanation. :)

    you are right. that two field is primary key and same meaning but value is different. Since the value is different, I can not make the connection. But this two field also need to connected. otherwise I cant separate the unique row of those table. The visittype field contain three types of data. Initial, full, compliance. and the visit table contain 1,2 and 3.

    Now when I need compare in trigger, I also need s.visittype =d.visit. But
     since the containing value is not same thats why I cant do it. What I need is an If statement,

    such as If visittype='Initial' then var=1 , If visittype='Full' then var=2......

    So that I can compare this field also with visit.

    I dont know how to do it. Did I make it clear?

    Sample data

    Visittype

    Initial

    full

    compliance

    Initial

    compliance

    full

    initial

    full

    Visit

    1

    2

    3

    1

    1

    3

    2

    I tried to show the data.

    Thanks for reply.


    Tuesday, September 2, 2014 5:08 PM
  • sorry for my poor explanation. :)

    you are right. that two field is primary key and same meaning but value is different. Since the value is different, I can not make the connection. But this two field also need to connected. otherwise I cant separate the unique row of those table. The visittype field contain three types of data. Initial, full, compliance. and the visit table contain 1,2 and 3.

    Now when I need compare in trigger, I also need s.visittype =d.visit. But
     since the containing value is not same thats why I cant do it. What I need is an If statement,

    such as If visittype='Initial' then var=1 , If visittype='Full' then var=2......

    So that I can compare this field also with visit.

    I dont know how to do it. Did I make it clear?

    Thanks for reply.

    ok for that you can create a derived column and join using it 

    ie like this

    ALTER TRIGGER [dbo].[ROSC10_Semister1]
    ON [dbo].[Student_ROSC10]
    FOR INSERT 
    AS 
    BEGIN
    
    MERGE Student AS d
    USING (SELECT DistrictID,upazilaID,LCID,EduYr,
    AdmClass,RollNo,EduYr,
    CASE VisitType
    WHEN 'Initial' THEN 1
    WHEN 'Full' THEN 2
    WHEN 'Compliance' THEN 3
    END AS Visit
    FROM INSERTED) AS s
    ON s.DistrictID = d.DistrictID and s.upazilaID= d.upazilaID and s.LCID=d.LCID and s.EduYr=d.StuYr and s.AdmClass=d.StuAdmClass AND s.Visit = d.Visit
    WHEN MATCHED THEN
    UPDATE 
    SET ROSC10_Status=1 
    WHEN NOT MATCHED THEN
    INSERT (DistrictID,upazilaID,LCID,RollNo,StuYr,StuAdmClass,ROSC10_Status)
    VALUES ( DistrictID, UpazilaID, LcID,RollNo,EduYr, AdmClass,1);
    END


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Soulidentities Wednesday, September 3, 2014 4:51 AM
    Tuesday, September 2, 2014 5:15 PM
  • Thanks a lot.

    I was trying to do this but didn't achieve. I am trying this way.

    Thanks for giving me time.

    Wednesday, September 3, 2014 4:51 AM
  • Thanks a lot.

    I was trying to do this but didn't achieve. I am trying this way.

    Thanks for giving me time.

    No problem

    you're welcome


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, September 3, 2014 7:06 AM
  • can you please correct it.I am getting error.

    I want to check the visittype and LCstatus value. If not satisfy then no execution happen.

    Thanks :)

    ALTER TRIGGER [dbo].[Insert_MoinitoringBasic]
    ON [dbo].[M0_BasicInfo]
    FOR INSERT,UPDATE
    AS 
    BEGIN
    IF (VisitType!='-') OR (LcStatus!='-')
    BEGIN
    
    MERGE [MonitoringROSCII].[dbo].[MonitorBasicInfo] AS d
    USING (SELECT DistrictID,upazilaID,LC_ID,AcademicYear,Trimester,RepID,IfCloseWhy,OthersSpecify,
    FirstVisitDate,SecondVisitDate,ThirdVisitDate,
    CASE VisitType 
    WHEN 'Initial validation' THEN 1
    WHEN 'Full validation' THEN 2
    WHEN 'Compliance monitoring' THEN 3
    END AS VisitTp,
    CASE LcStatus 
    WHEN 'Open' THEN 1
    WHEN 'Close on visit day' THEN 2
    WHEN 'Permanent close' THEN 3
    END AS StatusLC
     from INSERTED) AS s
    ON s.DistrictID = d.DistrictID and s.upazilaID= d.upazilaID and s.LC_ID=d.LCID and s.AcademicYear=d.LCVisitYr 
    and s.Trimester=d.Trimister and s.RepID = d.MOID and s.VisitTp=d.VisitType
    WHEN MATCHED THEN UPDATE SET DistrictID=S.DistrictID
    WHEN NOT MATCHED THEN
    INSERT (DistrictID, UpazilaID, LCID, VisitType, LCVisitYr, Trimister, MOID,  
     LCStatus, IfCloseWhy, OthersSpecify, LC1stVstDt,LC2ndVstDt,LC3rdVstDt)
    VALUES ( DistrictID, UpazilaID, Lc_ID,VisitTp,AcademicYear,Trimester,RepId,StatusLC,IfCloseWhy,OthersSpecify,
    FirstVisitDate,SecondVisitDate,ThirdVisitDate);
    END
    
    END

    Thursday, September 4, 2014 4:46 AM