Hot answers tagged

18

You can handle both INSERT and UPDATE with an EXCEPT set operator. The EXISTS will only evaluate to TRUE both if it's just an INSERT, or if it's an UPDATE with different values for any of these columns. IF EXISTS ( SELECT First_Name, Last_Name, JobCoe, Inactive FROM inserted EXCEPT SELECT First_Name, Last_Name, JobCoe, ...


12

The sys.triggers table does not expose this data, instead we have two options: OBJECTPROPERTY Using the OBJECTPROPERTY metadata function we can return the action as a property as below: OBJECTPROPERTY(object_id, 'ExecIsUpdateTrigger') OBJECTPROPERTY(object_id, 'ExecIsInsertTrigger') OBJECTPROPERTY(object_id, 'ExecIsDeleteTrigger') This will return a ...


9

When the update statement qualifies for a trivial plan, the optimizer rule that expands the instead-of trigger part of the statement (ExpandInsteadOfTriggerUpd) includes the part of the plan that reads from the base table. This rewrite includes adding an UPDLOCK hint to the base read. As usual, the UPDLOCK hint means that update locks are taken and held to ...


6

are insert triggers on that table reliable to fire every single time? Absolutely. Triggers aren't optional, they WILL fire. Triggers CAN cause a performance impact on highly transactional tables because the trigger fires synchronously to your DML operation. On an insert trigger, if the trigger execution fails, your insert fails. You won't get confirmation ...


5

I think I have captured what you need in this basic design: db<>fiddle ElementBase Self-fk for the hierarchy: CREATE TABLE dbo.ElementBase ( id integer NOT NULL, parent_id integer NOT NULL, element_type char(1) NOT NULL, -- id key CONSTRAINT [PK dbo.ElementBase id] PRIMARY KEY CLUSTERED (id), -- fk target ...


5

Your issue is related to the nested triggers notion in SQL Server. This concept is explained as like below in MSDN Both DML and DDL triggers are nested when a trigger performs an action that initiates another trigger. These actions can initiate other triggers, and so on. DML and DDL triggers can be nested up to 32 levels. Particularly your issue you can ...


4

To answer the question directly, the problem is: WHERE MyTable.GroupId = GroupId You haven't specified which GroupId you mean on the right hand side of that expression. The SQL standard dictates that SQL Server must resolve it to the innermost scope, so you have effectively written: WHERE MyTable.GroupId = MyTable.GroupId You should instead write: ...


4

This isn't really an "apples to apples" comparison. The trigger version of the query looks like this: UPDATE dbo.Subscriber SET IdSubscribersStatus = 1 FROM @Subscribers s2 JOIN dbo.Subscriber s ON s2.IdSubscriber = s.IdSubscriber OPTION(force order, fast 1) And the manual version of the query looks like this: update dbo.Subscriber set ...


4

You're close. AFTER triggers happen after foreign key constraint checking. So you need an INSTEAD OF trigger. That way you can modify the child tables before performing the DELETE on the target table. eg -- TABLE t_parent CREATE TABLE t_parent ( m_id INT IDENTITY PRIMARY KEY NOT NULL, m_name nvarchar(450) ); -- TABLE t_child CREATE TABLE t_child ...


4

Referencing Diagnostic Connection for Database Administrators with highlighting mine: SQL Server provides a special diagnostic connection for administrators when standard connections to the server are not possible. This diagnostic connection allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when ...


3

If you take time to run mysqldump, even with all default arguments, you will see that triggers in the dump file are created after all insert statements that populate your tables. So no, there will be no duplication, your data after restoring the dump will be the same as in the source database.


3

To further expand on my comment, here is some sample code for you to look at. I don't like the idea of introducing intentional locking on what appears to be a core table in your system. It will effectively slow everyone down to a single-threaded access. The ideal solution would remove the need to have the update from and update to logging actions in a ...


3

Manual:https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT statement_timestamp() / now() returns the start time of the current statement (more specifically, the time of receipt of the latest command message from the client). clock_timestamp() returns the actual current time, and therefore its value changes even within ...


3

It really sounds like you have an XY Problem. Your Business Requirement can be summed up with this statement: I don't care about data, just structural changes. I'm sure the Business Requirement to capture and manage Schema deltas is part of DevOps. There are solutions that exist for this part of the entire DevOps process. (eg Liquibase) I suggest you ...


3

I don't know of any built-in solution to this; I think you ultimately need to run your own CREATE TRIGGER statement for each new partition. There are a couple of ways to automate this. This function will come in handy: CREATE FUNCTION add_trigger(partition_id regclass) RETURNS VOID AS $$ DECLARE partition_name TEXT; BEGIN partition_name = (SELECT ...


3

Declare the no-op version as an SQL function then it will be in-lined as a constant value by the optimiser. CREATE OR REPLACE FUNCTION notify_conditionally(channel text, payload text) RETURNS int LANGUAGE sql AS $inner$ SELECT 0; $inner$;


3

The base table insert plan incorporates operations necessary to keep the indexed view synchronized with the base table as defined by the view definition. This part of the plan is automatically generated and cannot be disabled, or made to call the instead of trigger logic on the view instead. The contract of an indexed view is that it will always materialize ...


3

I think you will want something like the following - see the fiddle here. It's based on Common Table Expressions (CTEs) and that fact that with PostgreSQL, you can perform not only SELECTs, but also INSERTs, UPDATEs and DELETEs (see here also). First, your service table: CREATE TABLE service ( name VARCHAR (10) NOT NULL PRIMARY KEY, id INTEGER NOT ...


3

To clarify on @McNets comment: The error isn't produced by SQL Server itself. Somebody created a trigger on the table, a trigger that fires on UPDATE. This trigger apparently does a check for number of rows and if above a certain threshold, it generates this error and does a rollback. You should try to find who is responsible for the trigger and take it from ...


2

CREATE TABLE A ( ID1 INT, ID2 INT, ID3 INT, FOO INT, BAR INT, PRIMARY KEY (ID1, ID2, ID3) ); CREATE TABLE B ( ID1 INT, ID2 INT, ID3 INT, FOO INT, BAR INT, PRIMARY KEY (ID1, ID2, ID3) ); GO CREATE TRIGGER TRG_A_INSDEL ON [A] AFTER INSERT, DELETE AS BEGIN /* Insert new rows */ ...


2

This is not very complicated to do. If a BEFORE trigger returns a NULL value, this prevents the UPDATE from taking place. So in the trigger function you can check the conditions if an UPDATE is allowed. If not, Run an UPDATE statement that marks the row as inactive and do an INSERT. Something along the lines (not tested!): create or replace function ...


2

I think you could get it with a single statement. CREATE TRIGGER t1_insert ON T1 AFTER INSERT AS BEGIN IF EXISTS(SELECT 1 FROM inserted JOIN ( SELECT ID FROM T2 UNION ALL SELECT ID FROM T3 UNION ...


2

XEvents provide another way for getting known a T-SQL stack although SQL Server 2008 mightn't support a used event type. The solution consists of a trigger, an error and an XEvent session. I took Jim Brown's example to show the way it works. First of all, I tested the solution for SQL Server 2016 SP2CU2 Dev Edition. SQL Server 2008 supports some EXevent, ...


2

In case an update can affect multiple rows, you have to protect against two things: We want to consider updates that swap values between similar rows. If there are two John Smiths who need their JobCodes updated (first John from 1 to 2; second John from 2 to 1), we need to be careful to say they both were updated. We only want to log the changed rows in ...


2

You are using INSTEAD OF UPDATE, this means that each update will end in the trigger. You can add an ELSE statement to do the update if the first IF is not true. CREATE TRIGGER [dbo].[Prices_InsteadUpdate_Trigger] ON [dbo].[Prices] INSTEAD OF UPDATE AS SET NOCOUNT ON IF UPDATE(PRC_Value) AND EXISTS (SELECT * FROM deleted d JOIN inserted i ON d.PRC_PrcId=...


2

In general, non-transactional things (particularly those with human-visible side effects) should not be part of transactions. Sending emails, calling web services, etc. cannot be rolled back so you don't want them to be called directly from a trigger. For all your trigger knows, the transaction that did the insert might still be rolled back and your alert ...


2

You must use a function, not a procedure: CREATE FUNCTION pro_update_last_read() RETURNS trigger ... The trigger must be defined FOR EACH ROW, and you cannot pass columns to the trigger function. The way you access the columns in the trigger function is via the NEW variable: NEW.sensor_id and NEW.report_time.


2

The documentation has a list of the event attribute functions that you can access. It looks like you'd want ora_sysevent for the event, ora_dict_obj_name and ora_dict_obj_owner to identify the object in question and ora_dict_obj_type for the object type. Taking a step back, though, you'd normally solve this sort of problem by using source control to check ...


2

In the case of an INSERT, there is no previous version of the row, so the OLD variable is unassigned. See the documentation: OLD    Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is null in statement-level triggers and for INSERT operations.


2

It is possible. $ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 29 08:29:35 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 343933032 ...


Only top voted, non community-wiki answers of a minimum length are eligible