Solved

SQL Server 2000 Triggers on BULK INSERT

Posted on 2004-04-06
7
1,136 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

770 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