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

programer
Posting Yak Master

221 Posts

Posted - 2011-12-13 : 10:06:58

Hi,

I have two table:

tbl_table1 and tbl_table2.

Structure tbl_table1:
Id, Desc, IsApproved
1, Desc1, false
2, Desc2, false

Structure tbl_table2
Id, table1Id, Winnings
1, 1, false
2, 1, false
3, 1, true

In the first table in column IsApproved is="false" because
is the tbl_table2 column winnings is false.

How to update first table is in the table tbl_table2 Winning
Id, table1Id, Winnings
1, 1, true
2, 1, true
3, 1, true

If i have
Id, table1Id, Winnings
1, 1, true
2, 1, false
3, 1, true

is updated first table in the column IsApproved is="false"

So, i need with trigger update column IsApproved false or true depending in another table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 10:53:41

so table1 IsApproved will be true only if all corresponding records in table2 have Winnings = true?

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

programer
Posting Yak Master

221 Posts

Posted - 2011-12-13 : 11:31:07

quote:
Originally posted by visakh16

so table1 IsApproved will be true only if all corresponding records in table2 have Winnings = true?

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

Yes.
Help me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 11:43:36

something like


CREATE TRIGGER TrigUpdate
ON tbl_table2
FOR INSERT
AS
BEGIN
UPDATE t
SET t.IsApproved = CASE WHEN NonWinCnt> 0 THEN 'false' ELSE 'true' END
FROM tbl_table1 t
INNER JOIN (SELECT t2.table1ID, COUNT(CASE WHEN t2.Winnings = 'false' THEN 1 ELSE NULL END) AS NonWinCnt
            FROM (SELECT DISTINCT table1ID FROM INSERTED) i
            INNER JOIN table2 t2
            ON t2.table1ID = i.table1ID 
            GROUP BY t2.table1ID)i
ON i.table1ID = t.ID

END

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

programer
Posting Yak Master

221 Posts

Posted - 2011-12-13 : 12:21:29

quote:

Originally posted by visakh16

something like


CREATE TRIGGER TrigUpdate
ON tbl_table2
FOR INSERT
AS
BEGIN
UPDATE t
SET t.IsApproved = CASE WHEN NonWinCnt> 0 THEN 'false' ELSE 'true' END
FROM tbl_table1 t
INNER JOIN (SELECT t2.table1ID, COUNT(CASE WHEN t2.Winnings = 'false' THEN 1 ELSE NULL END) AS NonWinCnt
            FROM (SELECT DISTINCT table1ID FROM INSERTED) i
            INNER JOIN table2 t2
            ON t2.table1ID = i.table1ID 
            GROUP BY t2.table1ID)i
ON i.table1ID = t.ID

END

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

works thanks!.

Is it possible to call with stored procedure as trigger?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 12:25:17

hmm...trigger itself is a special type of procedure that will be triggered by DML/DDL operations

didnt understand why you want it to call as a separate procedure?

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

programer
Posting Yak Master

221 Posts

Posted - 2011-12-13 : 14:12:25

quote:
Originally posted by visakh16

hmm...trigger itself is a special type of procedure that will be triggered by DML/DDL operations

didnt understand why you want it to call as a separate procedure?

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

Because someone wrote me that in my case I prefer to use Stored Procedures to reduce burden.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 23:39:44

then how will you determine when to call this procedure?
making it a trigger will ensure it will get executed automatically upon each insert operation

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

programer
Posting Yak Master

221 Posts

Posted - 2011-12-14 : 04:24:52

quote:
Originally posted by visakh16

then how will you determine when to call this procedure?
making it a trigger will ensure it will get executed automatically upon each insert operation

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

this is true! thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 04:49:11

wc

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

Kristen
Test

22859 Posts

Posted - 2011-12-14 : 09:37:58

I think trigger should allow for UPDATE, and perhaps use EXISTS instead of SUM() for efficiency

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 10:17:16

which SUM? or do you mean COUNT() needs to be replaced by EXISTS? Ok that makes sense

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

Kristen
Test

22859 Posts

Posted - 2011-12-14 : 10:39:12

Sorry, yes meant COUNT() and not SUM()

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources