Multiple inserts in a single trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple inserts in a single trigger

Author

Topic

gautham.gn
Starting Member

19 Posts

Posted - 2014-02-10 : 13:36:30

I have a requirement that if in a table update happened based on 1st condition then it should insert in one way and if update happened on second condtion the insert statement will differ. That is it should insert the deleted records i.e., previous records existed in a table.

The syntax is like

CREATE TRIGGER [dbo].[tr_a] on [dbo].[A]
AFTER UPDATE
AS
BEGIN TRY
IF NOT EXISTS
(
SELECT * FROM INSERTED I INNER JOIN DELETED D ON
ISNULL(I.col1, 0) = ISNULL(D.col1, 0)
AND ISNULL(I.STATUS, 0) = ISNULL(D.STATUS, 0)
)
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.B
(
col0,
col1,
col2,
col3,
DETAILS,
DT
)
SELECT
A.col1,
A.col1,
A.col2,
'Session' col3,
A.STATUS,
GETDATE()
FROM
DELETED A WITH (NOLOCK)
END;
IF
(( SELECT count(*) FROM INSERTED I INNER JOIN DELETED D ON
ISNULL(I.col1, 0) = ISNULL(D.col1, 0)
AND ISNULL(I.charac, 0) = ISNULL(D.charac, 0) )
= 0 AND INSERTED.charac='N')
BEGIN
INSERT INTO dbo.EVENT_LOG
(
col0,
col1,
col2,
col3,
DETAILS,
DT
)
SELECT
A.col1,
A.col1,
A.col2,
'Session' col3,
'Session Closed' DETAILS,
GETDATE()
FROM
DELETED A WITH (NOLOCK)
END;
END TRY
BEGIN CATCH
SELECT Error_number()as Eror_Number ,error_message() as Error_message
END CATCH

GO

But., I m getting some syntactical errors. Can u please help me with this?
Thanks in advance,
gautham.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-02-10 : 13:49:29

What error are you getting? But, more importantly, what is the logic you are trying to implement (in words)?

gautham.gn
Starting Member

19 Posts

Posted - 2014-02-10 : 13:57:43

I m getting the error like The multi-part identifier "INSERTED.charac" could not be bound.
What I want to do in this is
1)If update happened in status column in table A, then it should insert the previous value in status column to table B.
2)If update happened to charac column in table A, it shuld insert the records and the possible values in charac column will be 'Y',
'N'.
And if only the charac column is updated from 'Y' to 'N' then only it should fire the trigger and insert into B table with the info given above in the query.

gautham.gn
Starting Member

19 Posts

Posted - 2014-02-10 : 14:20:08

I have a requirement that if in a table update happened based on 1st condition then it should insert in one way and if update happened on second condtion the insert statement will differ. That is it should insert the deleted records i.e., previous records existed in a table.

I m getting the error like The multi-part identifier "INSERTED.charac" could not be bound.
What I want to do in this is
1)If update happened in status column in table A, then it should insert the previous value in status column to table B.
2)If update happened to charac column in table A, it shuld insert the records and the possible values in charac column will be 'Y',
'N'.
And if only the charac column is updated from 'Y' to 'N' then only it should fire the trigger and insert into B table with the info given above in the query.

The syntax is like

CREATE TRIGGER [dbo].[tr_a] on [dbo].[A]
AFTER UPDATE
AS
BEGIN TRY
IF NOT EXISTS
(
SELECT * FROM INSERTED I INNER JOIN DELETED D ON
ISNULL(I.col1, 0) = ISNULL(D.col1, 0)
AND ISNULL(I.STATUS, 0) = ISNULL(D.STATUS, 0)
)
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.B
(
col0,
col1,
col2,
col3,
DETAILS,
DT
)
SELECT
A.col1,
A.col1,
A.col2,
'Session' col3,
A.STATUS,
GETDATE()
FROM
DELETED A WITH (NOLOCK)
END;
IF
(( SELECT count(*) FROM INSERTED I INNER JOIN DELETED D ON
ISNULL(I.col1, 0) = ISNULL(D.col1, 0)
AND ISNULL(I.charac, 0) = ISNULL(D.charac, 0) )
= 0 AND INSERTED.charac='N')
BEGIN
INSERT INTO dbo.B
(
col0,
col1,
col2,
col3,
DETAILS,
DT
)
SELECT
A.col1,
A.col1,
A.col2,
'Session' col3,
'Session Closed' DETAILS,
GETDATE()
FROM
DELETED A WITH (NOLOCK)
END;
END TRY
BEGIN CATCH
SELECT Error_number()as Eror_Number ,error_message() as Error_message
END CATCH

GO

But., I m getting some syntactical errors. Can u please help me with this?
Thanks in advance,
gautham.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-10 : 14:24:19

>>The multi-part identifier "INSERTED.charac" could not be bound

this is because once you define a table alias you must use that when referencing columns.

SELECT count(*) FROM INSERTED I INNER JOIN DELETED D ON
ISNULL(I.col1, 0) = ISNULL(D.col1, 0)
AND ISNULL(I.charac, 0) = ISNULL(D.charac, 0) )
= 0 AND INSERTED.charac='N'

Be One with the Optimizer
TG

gautham.gn
Starting Member

19 Posts

Posted - 2014-02-10 : 14:28:56

Even when i use I.charac also, it wont work then also it shows the same error.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-10 : 15:01:01

I see the problem:


...
IF
(      (SELECT count(*) 
       FROM INSERTED I 
       INNER JOIN DELETED D 
              ON ISNULL(I.col1, 0) = ISNULL(D.col1, 0)
              AND ISNULL(I.charac, 0) = ISNULL(D.charac, 0) 
       )= 0 
AND    INSERTED.charac='N')
BEGIN ...

this INSERTED.charac='N' has no FROM clause because it is just part of the IF expression.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources