Trigger - 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 2000 Forums

SQL Server Development (2000)

Trigger

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-07 : 10:11:52

Hassan writes "Hi,
I have two tables
1- CPA_tdta_Main (Fields are ID_CAPA,dt_-Primery Key- and Assignee_UserName)
2- CPA_tkey_Assignee (Fields are fi_CAPA_ID - This is Foreign key for ID_CAPA from CPA_tdta_Main table -,dt_Assignee_UserName)

I want to create an update trigger, to Update CPA_tkey_Assignee table when CPA_tdta_Main table is updated. I could have many fi_CAPA_ID.
This update should only take place where there is no record in CPA_tkey_Assignee table matching CPA_tdta_Main. I wrote the following trigger, but it is not working, it only insert when there is no record in CPA_tkey_Assignee, as soon as I have one record in CPA_tkey_Assignee, it does not up dated.
I would appreciated your help
Hassan

CREATE TRIGGER [InsertAssignee] ON [CPA_tdta_Main] 
FOR UPDATE
AS
IF EXISTS (SELECT CPA_tkey_Assignee.fi_CAPA_ID, 
    CPA_tkey_Assignee.dt_AssigneeName, 
    CPA_tkey_Assignee.dt_Assignee_UserName 'True'
FROM CPA_tdta_Main Right outer JOIN
    CPA_tkey_Assignee ON 
    CPA_tdta_Main.ID_CAPA = CPA_tkey_Assignee.fi_CAPA_ID )
BEGIN
  --This means it exists, return it to ASP and tell us
  SELECT 'This record already exists!'
END
ELSE
BEGIN
Insert CPA_tkey_Assignee(fi_CAPA_ID,dt_Assignee_UserName)
Select ID_CAPA,dt_Assignee_UserName


From inserted
END"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-07 : 10:36:18

You have an 'if exists' with an outer join and no where.
The outer join table is obviously not affting this clause - it is also better to have a select * rather than pick out fields.

I suspect you really want somethiong like

if exists (
select * from CPA_tkey_Assignee c, inserted i
where c.ID_CAPA = i.ID_CAPA and ...
)

==========================================
Cursors are useful if you don't know sql.
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