Solved

SQL Server 2000 Triggers on BULK INSERT

Posted on 2004-04-06
7
1,141 Views
Last Modified: 2007-12-19
Hi,

 I'm using SQL server that is included in a SCADA package called WinCC. This SCADA package also handles it's data in SQL server. Now I want to create a trigger on one of the tables created by the SCADA package. However I found out that this application uses BULK INSERT when it is adding information to that table. This means my trigger will not work because they did not use the FIRE_TRIGGER option when they declared the SQL statement.
 Does anybody know if there is a setting in SQL server that makes the FIRE_TRIGGER option default when using BULK INSERT??? Or does anybody have another solution?

Cheers,

Pieter
0
Comment
Question by:TrackMax
  • 3
  • 2
  • 2
7 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10767095
Not too much you can do without altering the Bulk Insert.  What does the trigger do, maybe there is another solution....
0
 

Author Comment

by:TrackMax
ID: 10772274
Depending on the value of one field enterred in the main table, the trigger copies or updates data in a different table in a different database. I copied the trigger code in the bottom:

CREATE TRIGGER Trig_AlarmHistoryCopy ON [dbo].[MsArcLong]  AFTER INSERT
AS
      declare @iMsgNr int
      declare @iState int
      declare @datAlarmTime datetime
      declare @fPValue1 float
      declare @fPValue2 float
      declare @fPValue3 float
      declare @fPValue4 float
      declare @fPValue5 float
      declare @fPValue6 float
      declare @fPValue7 float
      declare @fPValue8 float
      declare @fPValue9 float
      declare @fPValue10 float
      declare @szPText1 varchar(255)
      declare @szPText2 varchar(255)
      declare @szPText3 varchar(255)
      declare @szPText4 varchar(255)
      declare @szPText5 varchar(255)
      declare @szPText6 varchar(255)
      declare @szPText7 varchar(255)
      declare @szPText8 varchar(255)
      declare @szPText9 varchar(255)
      declare @szPText10 varchar(255)

      declare curNewAlarms scroll cursor for SELECT MsgNr,State,DateTime,
            PValue1,PValue2,PValue3,PValue4,PValue5,PValue6,PValue7,PValue8,PValue9,PValue10,
            PText1,PText2,PText3,PText4,PText5,PText6,PText7,PText8,PText9,PText10 FROM inserted;

      open curNewAlarms;

      fetch next from curNewAlarms into @iMsgNr,
            @iState,@datAlarmTime,
            @fPValue1,@fPValue2,@fPValue3,@fPValue4,@fPValue5,
            @fPValue6,@fPValue7,@fPValue8,@fPValue9,@fPValue10,
            @szPText1,@szPText2,@szPText3,@szPText4,@szPText5,
            @szPText6,@szPText7,@szPText8,@szPText9,@szPText10;

      WHILE @@FETCH_STATUS = 0
      BEGIN
            fetch next from curNewAlarms into @iMsgNr,
                  @iState,@datAlarmTime,
                  @fPValue1,@fPValue2,@fPValue3,@fPValue4,@fPValue5,
                  @fPValue6,@fPValue7,@fPValue8,@fPValue9,@fPValue10,
                  @szPText1,@szPText2,@szPText3,@szPText4,@szPText5,
                  @szPText6,@szPText7,@szPText8,@szPText9,@szPText10;

            IF @iState = 1
            BEGIN
                  insert into [TrackMaxRT].dbo.TM_AlarmHistory (MsgNr,CameIn,
                        PValue1,PValue2,PValue3,PValue4,PValue5,
                        PValue6,PValue7,PValue8,PValue9,PValue10,
                        PText1,PText2,PText3,PText4,PText5,
                        PText6,PText7,PText8,PText9,PText10)
                        VALUES( @iMsgNr,@datAlarmTime,
                        @fPValue1,@fPValue2,@fPValue3,@fPValue4,@fPValue5,
                        @fPValue6,@fPValue7,@fPValue8,@fPValue9,@fPValue10,
                        @szPText1,@szPText2,@szPText3,@szPText4,@szPText5,
                        @szPText6,@szPText7,@szPText8,@szPText9,@szPText10)
            END
            
            if @iState = 2
            BEGIN
                  update [TrackMaxRT].dbo.TM_AlarmHistory SET WentOut = @datAlarmTime
                        WHERE MsgNr = @iMsgNr AND WentOut IS NULL
            END

            if @iState = 3
            BEGIN
                  update [TrackMaxRT].dbo.TM_AlarmHistory SET Acknowledged = @datAlarmTime
                        WHERE MsgNr = @iMsgNr AND Acknowledged IS NULL
            END

            if @iState = 16
            BEGIN
                  update [TrackMaxRT].dbo.TM_AlarmHistory SET Acknowledged = @datAlarmTime
                        WHERE MsgNr = @iMsgNr AND Acknowledged IS NULL
            END
      
      END

      close curNewAlarms

****************************
I have also tried to apply this option

SET OPTION "PUBLIC"."FIRE_TRIGGERS" = 'ON'

However this option seems to be for Sybase
0
 
LVL 34

Accepted Solution

by:
arbert earned 250 total points
ID: 10774554
Ya, in SQL, as far as I know, there is no way around this without specifying the option on the Bulk Insert.

Can you setup a scheduled job that runs frequently and looks at the Alarm Date to insert new records into the second table?
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:TrackMax
ID: 10775338
Thanx for your help albert,

I already found a solution, not as I wanted it on a database trigger but code based. I would prefer to do it on the database because it does not take so much performance for the PC. However if it is not possible let's do it the quick and dirty way.

Best regards,

Pieter
0
 

Expert Comment

by:rsthompson
ID: 13568696
Pieter,

I am working on the same project trying the same thing that you did.  How did you accomplish this?  I would appreciate any information that you can give me on this issue so I don't have to waste years with trial and error on Wincc and DTS in SQL.  Thanks.

Ryan
0
 

Author Comment

by:TrackMax
ID: 13573880
Hello Ryan,

 To help you out: In the alarm logging of WinCC you must enable the property "triggers an action". This will call for the C script "GMsgFunction" in the standard functions tab of the global scripting. You can modify this function to store the event to a database.
 Copying the alarms via a database trigger is not possible anymore with WinCC version 6. Therefore there is no other solution than by the global scripting. There is one other option, but it will be time consuming and that is to use the OPC Alarm and Events of WinCC. I have not investigated this option, because it is hard to find good solid information about the OPC alarms and events servers.
I hope this help you out.

Best regards,

Pieter
0
 

Expert Comment

by:rsthompson
ID: 13574986
Pieter,

Could you send me a copy of your GMsgFunction script.  I am not a C programmer and have to try and learn it.  If you could send it to rsthompson@hvc.rr.com I would appreciate it.  Thanks for the info.

Ryan
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question