asked on

SQL Server 2000 Triggers on BULK INSERT

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

arbert

Not too much you can do without altering the Bulk Insert. What does the trigger do, maybe there is another solution....

TrackMax

ASKER

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

ASKER CERTIFIED SOLUTION

arbert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TrackMax

ASKER

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

rsthompson

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

TrackMax

ASKER

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

rsthompson

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