-1

content of csv file

TEST_1|sl2sysxbar301.dv.local|{'message_type': 2, 'super_evt_non_error': {'a0': 0, 'a1': 0, 'a2': 0, 'a3': 0, 'event': 5528, 'event_string': 'ENERGY_CTRL_RIGHT_PROX_DOWN', 'history_buffer_id': 3, 'history_buffer_string': 'HISTORY_BUFFER_UI_EVT', 'mid': 10, 'mid_string': 'CONSOLE1'}}|2021-06-26 05:03:20|
TEST_11|sl2sysxbar301.dv.local|{'message_type': 2, 'super_evt_non_error': {'a0': 393280, 'a1': 1, 'a2': 0, 'a3': 0, 'event': 8316, 'event_string': 'BP_FOLLOW_CHECK', 'history_buffer_id': 0, 'history_buffer_string': 'HISTORY_BUFFER_BP', 'mid': 6, 'mid_string': 'MTMR'}}|2021-06-26 05:03:20|
TEST_2|sl2sysxbar301.dv.local|{'message_type': 4, 'mmdsp_log': {'bp_start': {'bp_arg': 0.0, 'bp_code': 4097, 'bp_sn': 393300, 'mid': 6}, 'legacy_log_idx': 0}}|2021-06-26 05:03:20|
TEST_4|sl2sysxbar301.dv.local|{'message_type': 2, 'super_evt_non_error': {'a0': 267, 'a1': 0, 'a2': 0, 'a3': 0, 'event': 100001, 'event_string': 'INTER_MANIP', 'history_buffer_id': 3, 'history_buffer_string': 'HISTORY_BUFFER_UI_EVT', 'mid': 1, 'mid_string': 'AIM2'}}|2021-06-26 05:03:20|

here is bcp running via python

            p = subprocess.Popen(
                f'/opt/mssql-tools/bin/bcp {app.config.db_table_name} in {f.get("filename")}  -S {app.config.db_host},{app.config.db_port} -U {app.config.db_username} -P {app.config.db_password} -t  "|"  -c',
                shell=True,
                stdout=subprocess.PIPE,
                stderr=subprocess.STDOUT
            )

I have this table and trigger

CREATE TABLE [EVT_STREAM].[Event](
    [SystemName] [varchar](25) NOT NULL,
    [Router] [varchar](128) NULL,
  [Event] [nvarchar](max) NULL,
  [ReceivedAt] [datetime] NOT NULL,
  [InsertedAt] [datetime] NULL
)ON [PRIMARY]
GO

CREATE TRIGGER [EVT_STREAM].[trg_EVT_STREAM]
   ON [EVT_STREAM].[Event]
   FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
insert into [EVT_STREAM].[Event](
  [InsertedAt]
)
  VALUES(
    GETUTCDATE()
    )
END
GO

ALTER TABLE [EVT_STREAM].[Event] ENABLE TRIGGER [trg_EVT_STREAM]
GO

basically I would like to update [InsertedAt] at the time when BCP runs and inserts CSV records in [EVT_STREAM].[Event] table.

3 Answers 3

4

bcp has an option : FIRE_TRIGGERS link here (By default, triggers are not fired. To fire triggers explicitly, use the -h option with the FIRE_TRIGGERS hint.) -h"FIRE_TRIGGERS"

  • FIRE_TRIGGERS

Specified with the in argument, any insert triggers defined on the destination table will run during the bulk-copy operation. If FIRE_TRIGGERS is not specified, no insert triggers will run. FIRE_TRIGGERS is ignored for the out, queryout, and format arguments.

1

Based on MBuschi's suggestion:

Isn't better to create a default with getdate() for the field [InsertedAt]?

I was able to find an alternate solution w/o need of having a separate trigger:

CREATE TABLE [EVT_STREAM].[Event](
[SystemName] [varchar](25) NOT NULL,
[Router] [varchar](128) NULL,
[Event] [nvarchar](max) NULL,
[ReceivedAt] [datetime] NOT NULL,
[InsertedAt] [datetime] NULL
)ON [PRIMARY]
GO

ALTER TABLE [EVT_STREAM].[Event]
ADD CONSTRAINT df_inserted_at
DEFAULT GETUTCDATE() FOR InsertedAt
GO
0

Isn't better to create a default with getdate() for the field [InsertedAt]?

1
  • @MBuschi, I changed my approach and used, getdate() for the field [InsertedAt] it's working as intended. and solves my use case.
    – vector8188
    Commented Jun 26, 2021 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.