How to Get Use Text Columns in a Trigger

How to get use text columns in a trigger

the only way to do this is keep the current row in the history table too. Do what you tried and join to the actual table, and insert all columns from current actual table into the history table.

you probably have something like this, where the history only has the previous version of a row:

YourTable
ID value1 value2
1 AAA AAAA
2 BBB BBBB
3 CCC CCC3

YourTableHostory
HistoryID HistoryDate ID value1 value2
1 4/17/2010 2 CCC CCCC
2 4/18/2010 2 CCC CCC1

I'm saying do something like this, where every version is stored:

YourTable
ID value1 value2
1 AAA AAAA
2 BBB BBBB
3 CCC CCC3

YourTableHostory
HistoryID HistoryDate ID value1 value2
1 4/10/2010 1 AAA AAAA
2 4/10/2010 2 BBB BBBB
3 4/10/2010 3 CCC CCCC
4 4/17/2010 2 CCC CCC1
5 4/18/2010 2 CCC CCC2
5 4/19/2010 2 CCC CCC3

this way whenever there is an insert or update, just insert the current row into the history table, you have a duplicate of the current row, but that isn't that terrible.

If you are just adding this trigger to an existing table with data in it, then run a query like this to prepopulate all the current values for you:

INSERT INTO YourTableHostory
(HistoryDate,ID,value1,value2)
SELECT
GETDATE(),ID,value1,value2
FROM YourTable

SQL Server 2008 R2: Trigger on `TEXT` column

Since the trigger is fired after the insert, you can simply query back to the EmployeeMaster to get the inserted data. Something like this:

CREATE TRIGGER [dbo].[EmployeeMaster_Insert]
ON [dbo].[EmployeeMaster]
FOR INSERT
AS

INSERT INTO [dbo].[EmployeeMaster_Audit] ([EmpID], [EmpName], [EmpPhone], [EmpAddress])
SELECT EM.[EmpID]
, EM.[EmpName]
, EM.[EmpPhone]
, CONVERT(varchar(max), EM.[EmpAddress]) AS [EmpAddress]
FROM INSERTED I
INNER JOIN dbo.[EmployeeMaster] EM
ON EM.[EmpID] = I.[EmpID]
GO

This is assuming you cannot change the text datatype, see Zohar's answer.

Use a mysql trigger to replace text in column

CREATE TRIGGER tr_bi_order
BEFORE INSERT
ON order
FOR EACH ROW
SET NEW.payment_method = CASE WHEN NEW.payment_method LIKE '%Bank transfer%'
THEN 'Bank'
ELSE NEW.payment_method
END;

And the same trigger on BEFORE UPDATE event.

How to manipulate the TEXT column of DELETED/INSERTED tables in SQL Server trigger

I decided to do something else, I don't really like it, but it allows me to use a varchar column instead of the text column. So I did this :

ALTER TABLE [MyTable]
ADD [MyVarcharColumn] AS (CONVERT([nvarchar(MAX)],[MyTextColumn]))

With this computed column, I don't need to edit any existing queries or .EDMX (Entity Framework) of an existing project, and my trigger works fine.

How to manipulate TEXT, NTEXT data from sql server trigger

Don't use text or ntext. Use nvarchar(max) or varchar(max)

Harsh, but they have been deprecated for a reason...

Edit, just remembered. You may be able to use INSTEAD OF triggers

Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables

A nice solution has been found:

  1. SELECT FROM INSERTED just id column (it's not ntext or image and query is being executed).
  2. SELECT from original table * with the same ids.
  3. If required, use UPDATED() on INSERTED to be aware, what columns have been changed.

How to use text, ntext, or image columns in the 'inserted' and 'deleted' tables

As of SQL Server 2005, TEXT/NTEXT/IMAGE are deprecated - you should use the (N)VARCHAR(MAX) and VARBINARY(MAX) data types instead.

(N)VARCHAR(MAX) (see MSDN docs here) and VARBINARY(MAX) allow up to 2 GByte of data

From the MSDN docs:

nvarchar [ ( n | max ) ]

Variable-length Unicode character data. n can be a value from 1 through
4,000. max indicates that the maximum storage size is 2^31-1 bytes. (= 2 GB)

The (N)VARCHAR(MAX) types also allow all the usual T-SQL string function to work on them - something that wasn't the case with (N)TEXT at all.

As this MSDN article shows, the replacement types are supported in triggers, too:

SQL Server 2008 does not allow for
text, ntext, or image column
references in the inserted and deleted
tables for AFTER triggers. However,
these data types are included for
backward compatibility purposes only.
The preferred storage for large data
is to use the varchar(max),
nvarchar(max), and varbinary(max) data
types. Both AFTER and INSTEAD OF
triggers support varchar(max),
nvarchar(max), and varbinary(max) data
in the inserted and deleted tables.

Within a trigger function, how to get which fields are being updated

If a "source" doesn't "send an identifier", the column will be unchanged. Then you cannot detect whether the current UPDATE was done by the same source as the last one or by a source that did not change the column at all. In other words: this does not work properly.

If the "source" is identifiable by any session information function, you can work with that. Like:

NEW.column = session_user;

Unconditionally for every update.

General Solution

I found a way how to solve the original problem.

Set the column to a default value if it's not targeted in an UPDATE (not in the SET list). Key element is a per-column trigger introduced with PostgreSQL 9.0 - a column-specific trigger using the UPDATE OFcolumn_name clause. The manual:

The trigger will only fire if at least one of the listed columns is
mentioned as a target of the UPDATE command.

That's the only simple way I found to distinguish whether a column was updated with a new value identical to the old, versus not updated at all.

One could also parse the text returned by current_query(). But that seems cumbersome, tricky and unreliable.

Trigger functions

I assume a column source defined NOT NULL.

Step 1: Set source to NULL if unchanged:

CREATE OR REPLACE FUNCTION trg_tbl_upbef_step1()
RETURNS trigger
LANGUAGE plpgsql AS
$func$
BEGIN
IF NEW.source = OLD.source THEN
NEW.source := NULL; -- "impossible" value (source is NOT NULL)
END IF;

RETURN NEW;
END
$func$;

Step 2: Revert to old value. Trigger will only be fired, if the value was actually updated (see below):

CREATE OR REPLACE FUNCTION trg_tbl_upbef_step2()
RETURNS trigger
LANGUAGE plpgsql AS
$func$
BEGIN
IF NEW.source IS NULL THEN
NEW.source := OLD.source;
END IF;

RETURN NEW;
END
$func$;

Step 3: Now we can identify the lacking update and set a default value instead:

CREATE OR REPLACE FUNCTION trg_tbl_upbef_step3()
RETURNS trigger
LANGUAGE plpgsql AS
$func$
BEGIN
IF NEW.source IS NULL THEN
NEW.source := 'UPDATE default source'; -- optionally same as column default
END IF;

RETURN NEW;
END
$func$;

Triggers

The trigger for Step 2 is fired per column!

CREATE TRIGGER upbef_step1
BEFORE UPDATE ON tbl
FOR EACH ROW
EXECUTE PROCEDURE trg_tbl_upbef_step1();

CREATE TRIGGER upbef_step2
BEFORE UPDATE OF source ON tbl -- key element!
FOR EACH ROW
EXECUTE PROCEDURE trg_tbl_upbef_step2();

CREATE TRIGGER upbef_step3
BEFORE UPDATE ON tbl
FOR EACH ROW
EXECUTE PROCEDURE trg_tbl_upbef_step3();

db<>fiddle here

Trigger names are relevant, because they are fired in alphabetical order (all being BEFORE UPDATE)!

The procedure could be simplified with something like "per-not-column triggers" or any other way to check the target-list of an UPDATE in a trigger. But I see no handle for this, currently (unchanged as of Postgres 14).

If source can be NULL, use any other "impossible" intermediate value and check for NULL additionally in trigger function 1:

IF OLD.source IS NOT DISTINCT FROM NEW.source THEN
NEW.source := '#impossible_value#';
END IF;

Adapt the rest accordingly.



Related Topics



Leave a reply



Submit