Solved

SQL Server 2000 Triggers on BULK INSERT

Posted on 2004-04-06
7
1,130 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now