Triggers - 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

General SQL Server Forums

New to SQL Server Programming

Triggers

Author

Topic

Hanoon
Starting Member

4 Posts

Posted - 2007-04-14 : 19:04:07

hello everybody
i need some help with the update trigger, you see i have two tables each has a boolean column called status, i want to write a trigger on the table with the primary key "BussGroups" so that everytime this column is updated all columns in the foriegn table "BussForms" are also updated to the same value, can anyone help me asp pleeeeeeeeeeeeese

hanoony

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-14 : 23:05:42

Hanoony, you should either do this using cascading updates or not do it at all. Post the DDL for your tables.

e4 d5 xd5 Nf6

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-15 : 20:24:45

You have a bad table design. You should not have the Status column in the BussForms table at all.

Just store Status in the BussGroups table, and use a join between BussForms and BussGroups to lookup the Status from BussGroups.

CODO ERGO SUM

Hanoon
Starting Member

4 Posts

Posted - 2007-04-16 : 03:13:36

Thanks for your opinion Micheal, but unfortunatly that will not do, you see i am designing a general template that can be later used to organize any other applications, for that my tables have be as general as possible in order to cover all possibilities including the one where some forms of the bussiness group are not purchased in the package chosen by the customer.
As for blindman, many thanks, here are the dll for the stdBussGroup table:
GroupKey : varchar(10)
GroupEName: varchar(20)
GroupFName: varchar(20)
Status: bit
WhoInsert: numeric(18,0) "Foreign key on sysytem user table"
InsertDate: dateTime
WhoUpdate:numeric(18,0) "Foreign key on sysytem user table"
UpdateDate: dateTime

Here are the dll for the stdGroupForms table:
FormKey:varchar(10)
FormEName: varchar(20)
FormFName: varchar(20)
FormType: numeric(1,0) "Foreign key on another table"
Serial: init
Status: bit
WhoInsert: numeric(18,0) "Foreign key on sysytem user table"
InsertDate: dateTime
WhoUpdate:numeric(18,0) "Foreign key on sysytem user table"
UpdateDate: dateTime

If you have any questions, please don't hesitate.

hanoony

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-16 : 07:04:20

Believe me, if you have to make the kind of update you described, you have a bad table design.

You didn't really explain your design very well, or the reasoning behind what you are trying to do. The table layout you showed doesn't even contain the Foreign key to stdBussGroup, so it's hard to even understand how they are related.

CODO ERGO SUM

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-16 : 15:59:18

Believe me, if you are trying to come up with a "general database solution", every hour of redundant logic you avoid will require two hours of exception coding and four hours of debugging.
I too do not understand what you are trying to do, but I've read enough to make me nervous.

e4 d5 xd5 Nf6

Hanoon
Starting Member

4 Posts

Posted - 2007-04-16 : 20:12:43

I'll try to explain it better, but first i apologize for forgetting the foriegn key in table stdSysForms, so here are the correct ddl:
GroupKey : varchar(8) PK
GroupEName: varchar(20)
GroupFName: varchar(20)
Status: bit
WhoInsert: numeric(18,0) "Foreign key on sysytem user table"
InsertDate: dateTime
WhoUpdate:numeric(18,0) "Foreign key on sysytem user table"
UpdateDate: dateTime

Here are the dll for the stdGroupForms table:
FormKey:varchar(8)PK
GroupKey:varchar(8) FK
FormEName: varchar(20)
FormFName: varchar(20)
FormType: numeric(1,0) "Foreign key on another table"
Serial: init
Status: bit
WhoInsert: numeric(18,0) "Foreign key on sysytem user table"
InsertDate: dateTime
WhoUpdate:numeric(18,0) "Foreign key on sysytem user table"
UpdateDate: dateTime

Now the whole project is based on the idea of structuring a general database along with it's interfaces and their implementation (these represents the developer tool part of my application)that will be used to organize multiple groups of various Systems ;for example lets say that i developed AN ERP System, this system will be constructed as a group of modules,say: Accounting,Sales...etc.Each one of these modules are inserted in the stdBussGroups table. Also, each module has it's own set of interfaces "Forms" with their functionality, for example: The Sales group may have 2 forms:frmPointOfSale,frmOfficialBilling.Each form will be entered as a record in the stdSysForms table.Now if a company wants to purchase The ERP System but without the Sales module, all i have to do is to change the status field in the stdBussGroups table through the developer Tools which can only be accessed by authorized users "Usually the developer of the application only",hence all the forms that belong to this group with the group itself won't be available for this company.Moreover, the company may want the Sales module,however they only need the frmPointOfSale since it fits with buissness logic in that company, now all i have to do is set the status field in stdSysForms table to false and it's no longer available for this company.
Having said all that i already wrote a trigger, so if any of you can suggest some modifications i'd be gratefull,and please keep in ming i am just a graduate student.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE TRIGGER [dbo].[tr_stdBussGroups_UPDATE]
ON [dbo].[stdBussGroups]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for trigger here

--Make sure Status was changed
IF NOT UPDATE(status)
RETURN

--Determine if Status was changed to another value
IF EXISTS (SELECT *
FROM inserted a
JOIN deleted b ON a.BussGroupKey=b.BussGroupKey
WHERE b.status <> a.status )
BEGIN

DECLARE @BussGroupKey varchar(8)
SET @BussGroupKey=(SELECT a.BussGroupKey
FROM inserted a
JOIN deleted b ON a.BussGroupKey=b.BussGroupKey )

UPDATE stdSysForms
SET stdSysForms.status=(SELECT DISTINCT inserted.status
FROM inserted INNER JOIN stdSysForms
ON inserted.BussGroupKey=stdSysForms.BussGroupKey)
WHERE stdSysForms.BussGroupKey=@BussGroupKey

END

END

hanoony

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-17 : 00:52:07

Once again, your code does not account for multi-record transactions, and your use of a looping algorithm is poor SQL coding.
This whole section:

--Determine if Status was changed to another value
IF EXISTS (SELECT *
FROM inserted a
JOIN deleted b ON a.BussGroupKey=b.BussGroupKey
WHERE b.status <> a.status )
BEGIN

DECLARE @BussGroupKey varchar(8)
SET @BussGroupKey=(SELECT a.BussGroupKey
FROM inserted a
JOIN deleted b ON a.BussGroupKey=b.BussGroupKey )

UPDATE stdSysForms
SET stdSysForms.status=(SELECT DISTINCT inserted.status
FROM inserted INNER JOIN stdSysForms
ON inserted.BussGroupKey=stdSysForms.BussGroupKey)
WHERE stdSysForms.BussGroupKey=@BussGroupKey

END

Can and should be replace with this:

UPDATE	stdSysForms
SET	stdSysForms.status=inserted.status
FROM	stdSysForms
	inner join inserted on stdSysForms.BussGroupKey = inserted.BussGroupKey
	inner join deleted on inserted.BussGroupKey = deleted.BussGroupKey
where	inserted.status <> deleted.status

Hanoony, your whole application design is questionable to begin with, and definitely beyond your current coding skills. If you really want to continue with this project you need to get some competent developers and DBAs to help you.

e4 d5 xd5 Nf6

Hanoon
Starting Member

4 Posts

Posted - 2007-04-17 : 12:10:52

Thank you very much, you are really gifted with the ability you posses for writing these codes, i know that i am learning greatly from you, that being said, i find your words completely hurtful and judgemental, i am really good when it comes to writting codes in several programming languages, however i have a weakness in sql, which is why i joined this forum to begin with, i have a huge desire to learn it, so please for the future be more gentle, after all this section is denoted to unexperienced users in sql. Again my deepest thanks for the modification on the code, i am fasinated with your approach in your coding.

hanoony

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-17 : 14:19:39

What's hurtful? I stated a fact. You may be very accomplished at other coding languages, but obviously not with SQL. No shame in that, none of us were born knowing relational algebra. My point is just to make you aware that the task you are undertaking is too big for your current skillset.

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources