November 16, 2016 at 7:28 am
Let's say that I have 2 triggers on the same table.
If in the first trigger I update a row in the same table that has the trigger and in the second trigger I select this row from the table will I see the change?
What if I select from inserted pseudovariable. Will I see the change then?
For example let's say that I have a trigger on table works and it updates column sdate like this
update works
set sdate = getdate()
where works.id in
(select id from inserted) and
sdate is null
In the next trigger I run
select sdate
from works join
inserted on works.id = inserted.id
Should I see the new value of sdate?
If I select
select sdate
from inserted
again will I see the change?
PS. I set the order of the triggers with sp_settriggerorder
November 16, 2016 at 1:42 pm
It`s an example, not exactly what I want to do.
November 16, 2016 at 3:09 pm
Here is a way you can test.
CREATE TABLE dbo.Test (
Val INT NOT NULL
, sdate DATETIME NULL
);
CREATE TABLE dbo.Results (
id INT IDENTITY(1, 1) NOT NULL
, sdate DATETIME NULL
, descr VARCHAR(50)
);
GO
CREATE TRIGGER trg__test__insert__first
ON dbo.Test
AFTER INSERT
AS
BEGIN
INSERT INTO dbo.Results (
sdate
, descr
)
SELECT sdate
, 'First trigger before update'
FROM Inserted;
UPDATE dbo.Test
SET sdate = GETDATE();
INSERT INTO dbo.Results (
sdate
, descr
)
SELECT sdate
, 'First trigger after update'
FROM Inserted;
END;
GO
CREATE TRIGGER trg__test__insert__second
ON dbo.Test
AFTER INSERT
AS
BEGIN
INSERT INTO dbo.Results (
sdate
, descr
)
SELECT sdate
, 'Second trigger after update'
FROM Inserted;
INSERT INTO dbo.Results (
sdate
, descr
)
SELECT sdate
, 'Table Value'
FROM dbo.Test;
END;
GO
EXEC sp_settriggerorder @triggername = 'dbo.trg__test__insert__first'
, @order = 'First'
, @stmttype = 'INSERT';
GO
INSERT INTO dbo.Test (
Val
, sdate
)
VALUES ( 1, '2015-01-01' );
SELECT *
FROM dbo.Test;
SELECT *
FROM dbo.Results;
/* Results
idsdatedescr
12015-01-01 00:00:00.000First trigger before update
22015-01-01 00:00:00.000First trigger after update
32015-01-01 00:00:00.000Second trigger after update
42016-11-16 17:07:29.090Table Value
*/
November 20, 2016 at 9:52 am
Quick question, have you looked into sp_settriggerorder?
😎
November 20, 2016 at 1:50 pm
I have mentioned this in my question
November 21, 2016 at 9:42 am
>> I have 2 TRIGGERs on the same table. <<
The reason we put triggers in the SQL standards when it was on the committee was that the original SQL engines were built on top of filesystems. If you are doing things right. You should never write triggers again. It is a horrible procedural sub-language that we hated, but had no choice to include.
>> in the first TRIGGER I update a row in the same table that has the TRIGGER and in the second TRIGGER I select this row from the table will I see the change? <<
I would really have to go back to all of my ANSI X3 H2 notes to be sure, but as I recall, a trigger is executed and its results are marked as not committed. You should not see any changes until the work is actually committed. Are you starting to see why we really hate triggers?
>> What if I select from inserted pseudo_variable [sic: table] Will I see the change then?<<
This is why we have been trying for decades now to replace this procedural crap With declarative language.
In a declarative model of programming you specify what you want to be not how it is derived. The procedural triggers go step by step by step by step by step over and over until they come to some final state. Maybe. What is committed between each step in your triggers? What is available in the session?
Instead of trying to be the best gaslight engineer in North America, why did you learn about Thomas Edison and his incandescent light bulbs to become a pretty good electric light contractor?
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply