Trigger to Update/Insert Into Table Column

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

SQL Server 2005 Forums

Transact-SQL (2005)

Trigger to Update/Insert Into Table Column

Author

Topic

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-08-01 : 18:55:09

I want to create a trigger so that whenever there is an update, insert, or delete to a table called tbl_Audit, it will call a trigger to update another table called tbl_AuditPlan. The challenge is that I need to insert into a specific column based upon a column from a view called v_AuditInfo.

For example, if an audit is scheduled for Quarter 3, 2009 then I want to insert/update a record into tbl_AuditPlan.Q3, tbl_AuditPlan.Year. Then if there is another audit scheduled for Quarter 4, 2009 then I want it to insert/update tbl_AuditPlan.Q4, tbl_AuditPlan.year.

I need to format tblAuditPlan so that it appears like:
Group-- Q1 -----Q2 ------ Q3 ------ Q4 ------Year
A| Audit Info| Audit Info| Audit Info| Audit Info| 2011
B| Audit Info| Audit Info| Audit Info| Audit Info| 2011
C| Audit Info| Audit Info| Audit Info| Audit Info| 2011

The table layout of v_AuditInfo currently looks like
ID | Quarter | Year |Group| Audit Info|

I have a bit of code below, but it's not doing so well :o

[CODE]CREATE TRIGGER UpdateAuditPlan
ON dbo.tbl_Audit
AFTER UPDATE,INSERT,DELETE
AS

Select
case v_AuditInfo
when v_AuditInfo.ScheduledQuarter ='Q1' Then INSERT INTO tbl_AuditPlan (Q1, ScheduledYear,Group_Name) Select AuditInfo,Group_Name,ScheduledYear FROM v_AuditInfo
when v_AuditInfo.ScheduledQuarter ='Q2' Then INSERT INTO tbl_AuditPlan (Q2, ScheduledYear,Group_Name) Select AuditInfo,Group_Name,ScheduledYear FROM v_AuditInfo
when v_AuditInfo.ScheduledQuarter ='Q3' Then INSERT INTO tbl_AuditPlan (Q3, ScheduledYear,Group_Name) Select AuditInfo,Group_Name,ScheduledYear FROM v_AuditInfo
when v_AuditInfo.ScheduledQuarter ='Q4' Then INSERT INTO tbl_AuditPlan (Q4, ScheduledYear,Group_Name) Select AuditInfo,Group_Name,ScheduledYear FROM v_AuditInfo
END
FROM v_AuditInfo[/CODE]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-01 : 21:03:42

can you post the DDL for tbl_Audit, v_AuditInfo & tbl_AuditPlan ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-02 : 01:10:11

you cant include inserts like this inside case when.
it should be like:-


CREATE TRIGGER UpdateAuditPlan
ON dbo.tbl_Audit
AFTER UPDATE,INSERT,DELETE
AS
BEGIN
INSERT INTO tbl_AuditPlan (Q1,Q2,Q3,Q4,Group_Name, ScheduledYear) 
Select case v_AuditInfo
		when v_AuditInfo.ScheduledQuarter ='Q1' Then AuditInfo else null end,
case v_AuditInfo
		when v_AuditInfo.ScheduledQuarter ='Q2' Then AuditInfo else null end,

case v_AuditInfo
		when v_AuditInfo.ScheduledQuarter ='Q3' Then AuditInfo else null end,

case v_AuditInfo
		when v_AuditInfo.ScheduledQuarter ='Q4' Then AuditInfo else null end,

Group_Name,ScheduledYear 
FROM v_AuditInfo
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-08-02 : 11:33:12

quote:
Originally posted by khtan

can you post the DDL for tbl_Audit, v_AuditInfo & tbl_AuditPlan ?

KH
[spoiler]Time is always against us[/spoiler]

Here is the DDL

Thanks!

	CREATE TABLE tbl_Audit
	(
	Audit_ID smallint identity(1,1),
	Group_Name varchar(100),
	ScheduledQuarter varchar(5) not null, 
	ScheduledYear smallint not null,
	ScheduleDate smalldatetime,
	AuditStatus varchar(100),
	Primary key clustered (Audit_ID),
	Unique (Group_Name, ScheduledYear, ScheduledQuarter)
	)


	CREATE TABLE tbl_AuditPlan
	(
	Q1 varchar (500),
	Q2 varchar (500),
	Q3 varchar (500),
	Q4 varchar (500),
	ScheduledYear smallint, 
	Group_Name varchar (100)
	)


CREATE VIEW v_AuditInfo
AS
select Audit_ID,Dept_Name, tbl_Audit.Group_Name,Supervisors,DepartmentManager,Key_Processes, ScheduledQuarter, ScheduledYear,AuditStatus,ISNULL(Auditors,'')  + CHAR(13) + '  (#' + CAST(Audit_ID as varchar(100)) + ')  ' +CHAR(13)+CHAR(10)  + ISNULL(CONVERT(varchar, ScheduleDate, 101), '')+ ' '  + CHAR(13) + CAST (AuditStatus as varchar (50))as AuditInfo
from tbl_audit inner join v_GroupInfo
on tbl_audit.Group_Name = v_GroupInfo.Group_Name
GO

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-08-02 : 11:35:50

quote:
Originally posted by visakh16

you cant include inserts like this inside case when.
it should be like:-

CREATE TRIGGER UpdateAuditPlan
ON dbo.tbl_Audit
AFTER UPDATE,INSERT,DELETE
AS
BEGIN
INSERT INTO tbl_AuditPlan (Q1,Q2,Q3,Q4,Group_Name, ScheduledYear)
Select case v_AuditInfo
when v_AuditInfo.ScheduledQuarter ='Q1' Then AuditInfo else null end,
case v_AuditInfo
when v_AuditInfo.ScheduledQuarter ='Q2' Then AuditInfo else null end,

case v_AuditInfo
when v_AuditInfo.ScheduledQuarter ='Q3' Then AuditInfo else null end,

case v_AuditInfo
when v_AuditInfo.ScheduledQuarter ='Q4' Then AuditInfo else null end,

Group_Name,ScheduledYear
FROM v_AuditInfo
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

When I try to execute the statement, I get the following error:

Incorrect syntax near '='.

Do you know what may be causing this? Thanks!!

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-08-02 : 11:51:59

I fixed the syntax with the following code:

CREATE TRIGGER UpdateAuditPlan
ON dbo.tbl_Audit
AFTER UPDATE,INSERT,DELETE
AS
BEGIN
INSERT INTO tbl_AuditPlan (Q1,Q2,Q3,Q4,Group_Name, ScheduledYear) 
Select case v_AuditInfo.ScheduledQuarter
		when 'Q1' Then AuditInfo else null end,
case v_AuditInfo.ScheduledQuarter
		when 'Q2' Then AuditInfo else null end,

case v_AuditInfo.ScheduledQuarter
		when 'Q3' Then AuditInfo else null end,

case v_AuditInfo.ScheduledQuarter
		when 'Q4' Then AuditInfo else null end,

Group_Name,ScheduledYear 
FROM v_AuditInfo
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-02 : 11:54:53

it should be:-


CREATE TRIGGER UpdateAuditPlan
ON dbo.tbl_Audit
AFTER UPDATE,INSERT,DELETE
AS
BEGIN
INSERT INTO tbl_AuditPlan (Q1,Q2,Q3,Q4,Group_Name, ScheduledYear) 
Select case 
		when v_AuditInfo.ScheduledQuarter ='Q1' Then AuditInfo else null end,
case 
		when v_AuditInfo.ScheduledQuarter ='Q2' Then AuditInfo else null end,

case 
		when v_AuditInfo.ScheduledQuarter ='Q3' Then AuditInfo else null end,

case 
		when v_AuditInfo.ScheduledQuarter ='Q4' Then AuditInfo else null end,

Group_Name,ScheduledYear 
FROM v_AuditInfo
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-08-02 : 11:59:27

I found a piece of code that may be helpful. I don't completely understand the logistics of it so I'm going to do some research on it. But would this be an applicable solution to my problem?

declare @t3 table (AccountId uniqueidentifier, phonenumber varchar(255))             
insert into @t3            
    select '11111111-1111-1111-1111-111111111111', '111-111-1111' union            
    select '11111111-1111-1111-1111-111111111111', '111-222-2222' union            
    select '11111111-1111-1111-1111-111111111111', '111-333-3333' union            
    select '22222222-2222-2222-2222-222222222222', '222-111-1111' union            
    select '22222222-2222-2222-2222-222222222222', '222-222-2222' union            
    select '33333333-3333-3333-3333-333333333333', '333-111-1111'
select * from @t3
select  AccountID,             
            max(case when sno=1 then phonenumber else null end)as phone1,             
            max(case when sno=2 then phonenumber else null end) as phone2,             
            max(case when sno=3 then phonenumber else null end) as phone3,             
            max(case when sno=4 then phonenumber else null end) as phone4             
    from (  select  row_number() over(partition by AccountID order by AccountID) as sno ,            
                    *             
            from @t3) t3             
    group by AccountID

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-08-02 : 12:45:46

quote:

Originally posted by visakh16

it should be:-


CREATE TRIGGER UpdateAuditPlan
ON dbo.tbl_Audit
AFTER UPDATE,INSERT,DELETE
AS
BEGIN
INSERT INTO tbl_AuditPlan (Q1,Q2,Q3,Q4,Group_Name, ScheduledYear) 
Select case 
		when v_AuditInfo.ScheduledQuarter ='Q1' Then AuditInfo else null end,
case 
		when v_AuditInfo.ScheduledQuarter ='Q2' Then AuditInfo else null end,

case 
		when v_AuditInfo.ScheduledQuarter ='Q3' Then AuditInfo else null end,

case 
		when v_AuditInfo.ScheduledQuarter ='Q4' Then AuditInfo else null end,

Group_Name,ScheduledYear 
FROM v_AuditInfo
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

It works but I am getting a lot of duplicate records. Is there a way to resolve this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-02 : 13:04:44

how? can you explain?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-08-02 : 13:36:55

BEGIN

DECLARE @T1 Table (Q1 varchar(500), Q2 varchar (500), Q3 varchar (500), Q4 varchar (500), Group_Name varchar (100), ScheduledYear smallint)
INSERT INTO @T1 (Q1,Q2,Q3,Q4,Group_Name, ScheduledYear) 
Select case 
		when v_AuditInfo.ScheduledQuarter ='Q1' Then AuditInfo else null end,
case 
		when v_AuditInfo.ScheduledQuarter ='Q2' Then AuditInfo else null end,

case 
		when v_AuditInfo.ScheduledQuarter ='Q3' Then AuditInfo else null end,

case 
		when v_AuditInfo.ScheduledQuarter ='Q4' Then AuditInfo else null end,

Group_Name,ScheduledYear 
FROM v_AuditInfo

select * from @T1
order by ScheduledYear

select Group_Name,ScheduledYear,             
            max(case when sno=1 then Q1 else null end)as Q1,             
            max(case when sno=2 then Q2 else null end) as Q2,             
            max(case when sno=3 then Q3 else null end) as Q3,             
            max(case when sno=4 then Q4 else null end) as Q4             
    from (  select  row_number() over(partition by ScheduledYear order by ScheduledYear) as sno ,            
                    *             
            from @T1) t3             
    group by Group_Name,ScheduledYear 
END

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-08-02 : 13:42:19

quote:
Originally posted by visakh16

how? can you explain?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I found that the tbl_Audit already has a trigger on it. So it causes the trigger to loop. Also, since the trigger is an INSERT statement, it will still insert a record even if it's been created before. I think a trigger on a VIEW will work best for this situation (see post above)

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-08-02 : 16:43:56

Okay, right now I would like to create a view to make it look like:

-- Q1 ------ Q2 ------ Q3 ------ Q4 ----- Group ---Year
Audit Info|**Null***|**Null***| Audit Info|Accounting| 2011
**Null***|**Null***|**Null***| Audit Info|Accounting| 2012
**Null***|**Null***|**Null***| Audit Info|Finance|2011
Audit Info| Audit Info| Audit Info| Audit Info|Human Resources| 2011

[CODE]DECLARE @T1 Table (Q1 varchar(500), Q2 varchar (500), Q3 varchar (500), Q4 varchar (500), Group_Name varchar (100), ScheduledYear smallint)
insert into @t1
select 'NULL', 'Null', 'Audit info', 'Null', 'Information Services', '2011' union
select 'NULL', 'Audit info', 'Null', 'Null', 'Information Services', '2011' union
select 'NULL', 'Null', 'Null', 'Audit info', 'Information Services', '2011' union
select 'NULL', 'Null', 'Null', 'Audit info', 'Finance', '2011' union
select 'Audit Info', 'Null', 'Null', 'Null', 'Accounting', '2011' union
select 'NULL', 'Null', 'Null', 'Audit info', 'Accounting', '2011' union
select 'NULL', 'Null', 'Null', 'Audit info', 'Accounting', '2012' union
select 'Audit info', 'Null', 'Null', 'Null', 'Human Resources', '2011' union
select 'NULL', 'Audit info', 'Null', 'Null', 'Human Resources', '2011' union
select 'NULL', 'Null', 'Audit info', 'Null', 'Human Resources', '2011' union
select 'NULL', 'Null', 'Null', 'Audit info', 'Human Resources', '2011'

select * from @t1
order by scheduledyear, group_name[/CODE]

I have tried the code below, but it only works when the quarters are sequential starting from Q1. For example, (Q1,Q2,Q3) will work, but (Q2,Q4), (Q1, Q3,), (Q3,Q4) won't work. It will just display a row with null values.

[CODE]
select Group_Name,ScheduledYear,
max(case when sno=1 then Q1 else null end) as Q1,
max(case when sno=2 then Q2 else null end) as Q2,
max(case when sno=3 then Q3 else null end) as Q3,
max(case when sno=4 then Q4 else null end) as Q4
from ( select row_number() over(partition by ScheduledYear,Group_Name order by ScheduledYear,Group_Name) as sno ,
*
from @T1) t1
group by Group_Name,ScheduledYear [/CODE]

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2011-08-02 : 19:25:26

A solution was provided for me. Here's the code

SELECT  MAX(Q1) AS Q1,
        MAX(Q2) AS Q2,
        MAX(Q3) AS Q3,
        MAX(Q4) AS Q4,
        Group_Name,
        ScheduledYear
FROM    @T1
GROUP BY Group_Name,
        ScheduledYear
ORDER BY Group_Name,
        ScheduledYear ;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources