cris
Try this, if i understand you correctly, you want it to be rolled back when
there is pending for concerned id.
-- this is untested as no ddl is given
CREATE TRIGGER <TRIGGERNAME> ON <TABLENAME>
FOR UPDATE
AS
DECALRE @trxdetid int
select @trxdetid = trxdetid from inserted
IF EXISTS(SELECT STATUS FROM <TABLENAME> WHERE trxdetid = @trxdetid AND
STATUS = 'PENDING')
BEGIN
ROLLBACK TRANSACTION
END
---
Regards
R.D
[quoted text, click to view] "Chris" wrote:
> Hi,
> I have the following table that is updated by 2 depts
>
> trxdetid amt shp crdt status
> 123 2 1 1 closed
> 123 1 Pending
> 124 2 2 closed
> 125 2 2 closed
> 126 3 Pending
> 127 2 1 open
> 127 1 1 closed
>
>
> I want to place a trigger on the table to execute after update and to only
> execute an update statement in the trigger for a trxid when there is no crdt
> amount and status is not pending.
>
> so for
>
> 123 the update will not execute because one is pending
> 124 the update will execute because status is closed and crdt = 0 or nothing
> 125 the update will not execute because even though the status is closed
> crdt = 2
> 126 the update will not execute because the status is Pending
> 127 the update will execute because there is no crdt and status is open and
> closed
>
> so the only time the update statement will be executed in the trigger id if
> the trxdetid in question (eg 123) doesn't have a pending status and crdt = 0
>
> I hope I am making sense.
>
> Thanks
>
Do not write code like this in a trigger:
"select @trxdetid = trxdetid from inserted"
The trigger could fail if more than one row is updated.
Try:
CREATE TRIGGER trg_some_table_update ON some_table
FOR UPDATE
AS
IF EXISTS
(SELECT *
FROM some_table AS T
JOIN inserted AS I
ON T.trxdetid = I.trxdetid
AND T.status = 'PENDING')
BEGIN
RAISERROR('error... ', 16, 1)
ROLLBACK TRAN
END
GO
--
David Portas
SQL Server MVP
--
Hi,
I believe I found it......
CREATE TRIGGER trg_table1_update ON table1
FOR UPDATE
AS
If Not Exists(select status from dbo.table1
where trxdetid = 123 and status ='Pending')
Begin
If (select sum(crdt) from dbo.table1
where trxdetid = 123)=0
execute update statement
Else
exit here
End
Else
exit here
I am not sure. Have to test. How do I exit in the if statement?
[quoted text, click to view] "Chris" wrote:
> Hi,
> I have the following table that is updated by 2 depts
>
> trxdetid amt shp crdt status
> 123 2 1 1 closed
> 123 1 Pending
> 124 2 2 closed
> 125 2 2 closed
> 126 3 Pending
> 127 2 1 open
> 127 1 1 closed
>
>
> I want to place a trigger on the table to execute after update and to only
> execute an update statement in the trigger for a trxid when there is no crdt
> amount and status is not pending.
>
> so for
>
> 123 the update will not execute because one is pending
> 124 the update will execute because status is closed and crdt = 0 or nothing
> 125 the update will not execute because even though the status is closed
> crdt = 2
> 126 the update will not execute because the status is Pending
> 127 the update will execute because there is no crdt and status is open and
> closed
>
> so the only time the update statement will be executed in the trigger id if
> the trxdetid in question (eg 123) doesn't have a pending status and crdt = 0
>
> I hope I am making sense.
>
> Thanks
>
You don't need to "exit" the IF statement but you can't have END
without BEGIN. Like this:
IF 1=1
BEGIN
SELECT 1
END
ELSE
BEGIN
SELECT 2
END
If you need more help, please post real code instead of pseudo code. As
posted your trigger doesn't seem to make much sense so it's difficult
to see what you intended. DDL (CREATE TABLE statement) for the table
would also help.
--
David Portas
SQL Server MVP
--
I believe I found solution. Waiting to test. Will post results.
[quoted text, click to view] "David Portas" wrote:
> You don't need to "exit" the IF statement but you can't have END
> without BEGIN. Like this:
>
> IF 1=1
> BEGIN
> SELECT 1
> END
> ELSE
> BEGIN
> SELECT 2
> END
>
> If you need more help, please post real code instead of pseudo code. As
> posted your trigger doesn't seem to make much sense so it's difficult
> to see what you intended. DDL (CREATE TABLE statement) for the table
> would also help.
>
> --
> David Portas
> SQL Server MVP
> --
>
Don't see what you're looking for? Try a search.
