Trigger Help - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Trigger Help

Author

Topic

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2012-08-27 : 04:14:56

I want to create trigger ,for this query,how can ?

SELECT
CASE WHEN BA=1 THEN SUM(TEST_ROWS.AMOUNT) ELSE 0 END AS ALACAK,
CASE WHEN BA=1 THEN SUM(ISNULL(TEST_ROWS.AMOUNT_2,0)) ELSE 0 END AS ALACAK_2,
CASE WHEN BA=0 THEN SUM(TEST_ROWS.AMOUNT) ELSE 0 END AS BORC,
CASE WHEN BA=0 THEN SUM(ISNULL(TEST_ROWS.AMOUNT_2,0)) ELSE 0 END AS BORC_2,
TEST_ROWS.ACCOUNT_ID,
TEST.ACTION_DATE,
TEST.CARD_TYPE,
TEST.CARD_CAT_ID
FROM
TEST_ROWS,TEST
WHERE
BA IN (0,1) AND TEST.CARD_ID=TEST_ROWS.CARD_ID
GROUP BY
TEST_ROWS.ACCOUNT_ID,
TEST.ACTION_DATE,
TEST.CARD_TYPE,
TEST.CARD_CAT_ID,
TEST_ROWS.BA

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-27 : 04:31:19

What do you want the trigger to do and when do you want it to fire?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2012-08-27 : 04:39:41

insert update or delete test_rows table,

quote:
Originally posted by nigelrivett

What do you want the trigger to do and when do you want it to fire?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2012-08-27 : 04:43:50

I want to this query result into table for example CUMLATIVE TABLE,AFTER
I WANT TO CREATE TRIGGER ON TEST_ROWS AND TEST TABLE FOR UPDATE DELETE AND INSERT.THIS QUERY RESULT MUST BE SAME CUMLATIVE TABLE

quote:
Originally posted by nigelrivett

What do you want the trigger to do and when do you want it to fire?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-27 : 04:54:05

That would mean a trigger on both test and test_rows.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2012-08-27 : 05:03:00

YES

quote:
Originally posted by nigelrivett

That would mean a trigger on both test and test_rows.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-27 : 05:55:08

Here's one for test_rows

create trigger tr on test_rows after insert, update , delete
as
delete tbl_sum
from tbl_sum t
join (select ACCOUNT_ID from inserted union select ACCOUNT_ID from deleted) a
on a.ACCOUNT_ID t.ACCOUNT_ID

insert tbl_sum
SELECT
CASE WHEN BA=1 THEN SUM(TEST_ROWS.AMOUNT) ELSE 0 END AS ALACAK,
CASE WHEN BA=1 THEN SUM(ISNULL(TEST_ROWS.AMOUNT_2,0)) ELSE 0 END AS ALACAK_2,
CASE WHEN BA=0 THEN SUM(TEST_ROWS.AMOUNT) ELSE 0 END AS BORC,
CASE WHEN BA=0 THEN SUM(ISNULL(TEST_ROWS.AMOUNT_2,0)) ELSE 0 END AS BORC_2,
TEST_ROWS.ACCOUNT_ID,
TEST.ACTION_DATE,
TEST.CARD_TYPE,
TEST.CARD_CAT_ID
FROM
TEST_ROWS,TEST
WHERE
BA IN (0,1) AND TEST.CARD_ID=TEST_ROWS.CARD_ID
and TEST_ROWS.ACCOUNT_ID in ((select ACCOUNT_ID from inserted union select ACCOUNT_ID from deleted) )
GROUP BY
TEST_ROWS.ACCOUNT_ID,
TEST.ACTION_DATE,
TEST.CARD_TYPE,
TEST.CARD_CAT_ID,
TEST_ROWS.BA

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources