5

I want to trigger some SQL code just before some update in a table or just after the update in a table.

It seems like Triggers are not supported by Snowflake.

Any workaround will be appreciated.

Regards, Neeraj

Neeraj Kumar
  • 215
  • 3
  • 13

2 Answers2

8

triggers are indeed not supported by Snowflake but you can simulate the behaviour by using streams and tasks combination:

https://docs.snowflake.net/manuals/user-guide/streams.html - streams are used to track the tables for changes

https://docs.snowflake.net/manuals/user-guide/tasks-intro.html - tasks are used to execute stored procedures

MMV
  • 920
  • 4
  • 9
  • One thing I want to note here is that Stream only tracks deltas... if same row is changed 2x... it would only record changes from origin to final. the first change would be lost in the delta/stream table. Very unfortunate for our use case. – sam yi May 25 '21 at 15:10
0

Snowflake appears to offer some really cool features so it's unfortunate that a basic tool of every dba I know is missing. Triggers are great for enforcing business rules upon the application developers.

I've been thinking about what might help cover all use cases - and I'm currently leaning toward moving all insert/update/delete processes to a Stored Procedures (best practice anyway?) - then build the "trigger" activity directly in the SP. I dislike that this buries the events but I think it will get the job done. I also dislike that the number of SP's could grow out of hand.

Now I need to dig deeper to make sure the capability of column level data detection is an option. and/or can I chain SP's for code reuse. If chaining works, then put the trigger logic in it's own SP.

Work around at best - but need to think outside the box.

knightgambit
  • 166
  • 1
  • 7
  • Snowflake was designed as an analytical database (though I realise they have introduced some ‘transactional’ features more recently). Having triggers in an analytical DB (as opposed to building business logic into the ETL processes) is generally a really bad idea – NickW Dec 02 '22 at 00:53
  • I see your point and don't completely disagree but even in that space, in large orgs, it sure is nice to be able to enforce the rules you've given the analytics and app teams. Also, some processes in manufacturing, such as using kepware, seem to work better with machine -> kepware -> database... then do transformation if needed. Again, rules about the data coming in are easy with triggers. I get it though. Time to think differently. – knightgambit Dec 03 '22 at 02:00