December 10, 2007 at 3:54 pm
I'm pretty new to triggers and using case statement. I'm looking at modifing a previously created trigger to insert 2 more columns. PRIMARY_KEY_2=DEPTID AND PRIMARY_KEY_3=
Once the trigger fires on an update, insert/delete it then populates another table.
here's the current trigger:
CREATE TRIGGER PS_DEPT_TBL_TRG ON PS_DEPT_TBL
AFTER INSERT, UPDATE, DELETE
AS
INSERT PS_IWAY_CTL ([TABLE_NAME], [PRIMARY_KEY_1], [ACTION], [DATE_TIME])
SELECT 'PS_DEPT_TBL',
CASE WHEN I.SETID IS NOT NULL THEN I.SETID ELSE D.SETID END,
CASE WHEN I.SETID IS NULL THEN 'DELETE'
WHEN D.SETID IS NULL THEN 'INSERT'
ELSE 'UPDATE'
END,
GETDATE()
FROM INSERTED I
FULL OUTER JOIN DELETED D ON I.SETID = D.SETID
GO
What I'd like to add would be 2 more columns to be inserted with the 2 other index keys>
INSERT PS_IWAY_CTL ([TABLE_NAME], [PRIMARY_KEY_1], [b]PRIMARY_KEY_2[/b], [b]PRIMARY_KEY_3[/b], [ACTION], [DATE_TIME])
SELECT 'PS_DEPT_TBL',
**REST OF STATEMENT HERE***
Appreciate the help
December 10, 2007 at 4:03 pm
This is a very basic INSERT INTO...SELECT statement. You've already added in the column references into your INSERT INTO statement, you just need to add to the SELECT part what you want to populate those columns with. It looks like maybe DEPTID for the first one but you left off the value for the second. You're looking at something like this:
INSERT PS_IWAY_CTL ([TABLE_NAME], [PRIMARY_KEY_1], [PRIMARY_KEY_2], [PRIMARY_KEY_3], [ACTION], [DATE_TIME])
SELECT 'PS_DEPT_TBL',
CASE WHEN I.SETID IS NOT NULL THEN I.SETID ELSE D.SETID END,
DEPTID,
??????,
CASE WHEN I.SETID IS NULL THEN 'DELETE' WHEN D.SETID IS NULL THEN 'INSERT' ELSE 'UPDATE' END,
GETDATE()
FROM INSERTED I
FULL OUTER JOIN DELETED D ON I.SETID = D.SETID
GO
December 10, 2007 at 7:24 pm
I think we're close although when trying to execute I got Ambiguous column name 'DEPTID'. and Ambiguous column name 'EFFDT' (noted change in bold).
Here was the statement:
CREATE TRIGGER PS_DEPT_TBL_TRG ON PS_DEPT_TBL
AFTER INSERT, UPDATE, DELETE
AS
INSERT PS_IWAY_CTL ([TABLE_NAME], [PRIMARY_KEY_1], [PRIMARY_KEY_2], [PRIMARY_KEY_3],[ACTION], [DATE_TIME])
SELECT 'PS_DEPT_TBL',
CASE WHEN I.SETID IS NOT NULL THEN I.SETID ELSE D.SETID END,DEPTID,EFFDT,
CASE WHEN I.SETID IS NULL THEN 'DELETE'
WHEN D.SETID IS NULL THEN 'INSERT'
ELSE 'UPDATE'
END,
GETDATE()
FROM INSERTED I
FULL OUTER JOIN DELETED D ON I.SETID = D.SETID
GO
Is there something I may hav missed?
December 10, 2007 at 7:26 pm
Which DEPTID you need - from inserted or from deleted?
_____________
Code for TallyGenerator
December 11, 2007 at 8:51 am
Yep, pretty simple Peter. You'll need to prefix your columns with the appropriate alias as Sergiy is suggesting.
December 14, 2007 at 10:43 am
Actually, you probably want "isnull(I.DeptID, D.DeptID)", and the same for the other field, so that if it's an insert or update, it grabs it from Inserted, and if it's a delete, the converse.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 17, 2007 at 1:54 pm
Hey guys thnxs for all the help. I now have things working the way I'd hoped.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply