February 10, 2014 at 12:13 pm
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.
February 10, 2014 at 1:16 pm
You have one too many equals in your code. Also look up the use of IF UPDATE() within a trigger.
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))
-- One too many =
= 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
February 11, 2014 at 3:11 pm
CREATE TRIGGER [dbo].[tr_a] on [dbo].[A]
AFTER UPDATE
AS
SET NOCOUNT ON
BEGIN TRY
IF UPDATE(STATUS)
BEGIN
INSERT INTO dbo.B
(
col0,
col1,
col2,
col3,
DETAILS,
DT
)
SELECT
D.col1,
D.col1,
D.col2,
'Session' AS col3,
D.STATUS,
GETDATE() AS DT
FROM DELETED D WITH (NOLOCK)
INNER JOIN INSERTED I ON
I.col1 = D.col1 AND
ISNULL(I.STATUS, 0) <> ISNULL(D.STATUS, 0) --only insert if the value actually changed
END --IF
IF UPDATE(charac)
BEGIN
INSERT INTO dbo.B
(
col0,
col1,
col2,
col3,
DETAILS,
DT
)
SELECT
D.col1,
D.col1,
D.col2,
'Session' AS col3,
'Session Closed' AS DETAILS,
GETDATE() AS DT
FROM
DELETED D WITH (NOLOCK)
INNER JOIN INSERTED I ON
I.col1 = D.col1 AND
D.charac = 'Y' AND
I.charac = 'N'
END --IF
END TRY
BEGIN CATCH
-- can't return data from a trigger
-- you can instead INSERT into an error table if you want
--INSERT INTO dbo.error_table ( ... ) SELECT ERROR_NUMBER(), ...
END CATCH
GO
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply