?
Solved

SQL Server 2000 Triggers on BULK INSERT

Posted on 2004-04-06
7
Medium Priority
?
1,167 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 750 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

719 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