Help with CREATE TRIGGER syntax

Any help would be appreciated. What's wrong with the following syntax?
CREATE TRIGGER tr_CMR_Client_Status_Confirm
ON [CMR Client Numbers]
FOR INSERT, UPDATE
AS
IF UPDATE [Current Status]
CASE WHEN [Current Status] = 'X' THEN [Status Flag] = [CMR
Client Number ID] ELSE [Status Flag] = NULL END1. IF UPDATE should have parentheses around the column name - note: if
any row's [Current Status] is updated, this will evaluate to true.
2. not a syntax error, but for clarity, should put a BEGIN..END after the IF
3. [biggest problem] There's no DML statement to do anything, just a
CASE expression...
4. ... which is incorrectly written
Look up UPDATE, CREATE TRIGGER and CASE in BOL
It is quite unclear from the code given what table the [Status Flag]
column belongs to, otherwise I could give an example of possible correct
trigger code.
Please provide DDL of the table.
mike wrote:

>Any help would be appreciated. What's wrong with the following syntax?
>
>CREATE TRIGGER tr_CMR_Client_Status_Confirm
>ON [CMR Client Numbers]
>FOR INSERT, UPDATE
> AS
> IF UPDATE [Current Status]
> CASE WHEN [Current Status] = 'X' THEN [Status Flag] = [CMR
>Client Number ID] ELSE [Status Flag] = NULL END
>
>
>
>|||What are you trying to do? It looks like you want to duplicate a value in
another column of your table, which isn't a good thing to do.
Please post DDL and sample data.
David Portas
SQL Server MVP
--sql

help with clr trigger

hi all.

i'm writing a simple clr trigger.

the trigger reads values from the INSERTED table.

and i use try - catch statement.

in the catch () i want to inesrt a new row to my Logtbl Table.

how can i insert row to a table in another tables trigger?

can you please give me a simple example?

thanks.

How about: "insert into Logtbl values(your values)" as a SqlCommand.

Niels
|||

should i use the same connection object for this sqlCommand?

|||Sure.

Niels

help with arithmetic overflow error with insted of update trigger

Hi, I have a view set up with an INSTEAD OF UPDATE trigger specified.
When I perform an update on certain records, i.e.:
UPDATE mytableview
SET field1 = 1
WHERE userid = 1234
I am finding that *some* user id's result in the follwing error:
"Arithmetic overflow error converting expression to data type smalldatetime.
The statement has been terminated."
mytable view has a number of data fileds. All the data fields are of type
smalldatatime.
When i compare the user record of a userid that causes an error to one that
doesnt cause an error, the dates do vary, where some date fields have NULL's
or correctly formated smalldatatime values (yes I know about the restriction
of smalldatetime to range between 1900 and 2079).
The odd thing is that even if i am updating a non-date field within the
view, the above arithmetic error still occurs.
My trigger looks like the following:
CREATE TRIGGER mytrigger ON mytableview
INSTEAD OF UPDATE
AS
DECLARE @.mydate datetime
SELECT @.mydate = GETDATE()
UPDATE mytable SET
field1 = ISNULL(inserted.field1, 0),
field2 = ISNULL(inserted.field2, 0),
field3 = ISNULL(inserted.field3, 0),
date1 = inserted.date1,
date2 = @.date+30
FROM inserted
WHERE mytable.userid = inserted.userid
Am i getting this error because i am mixing a date2 fiels (which is of type
smalldatetime) with @.date (which is of type datetime) ?
Any help most appreciated.Do you need the extra ms or time range? If not try:
DECLARE @.mydate smalldatetime
SELECT @.mydate = GETDATE()
SELECT @.mydate
HTH
Jerry
"PWalker" <pwalker@.nospam.com> wrote in message
news:uCQYjxz0FHA.3256@.TK2MSFTNGP09.phx.gbl...
> Hi, I have a view set up with an INSTEAD OF UPDATE trigger specified.
> When I perform an update on certain records, i.e.:
> UPDATE mytableview
> SET field1 = 1
> WHERE userid = 1234
> I am finding that *some* user id's result in the follwing error:
> "Arithmetic overflow error converting expression to data type
> smalldatetime.
> The statement has been terminated."
> mytable view has a number of data fileds. All the data fields are of type
> smalldatatime.
> When i compare the user record of a userid that causes an error to one
> that doesnt cause an error, the dates do vary, where some date fields have
> NULL's or correctly formated smalldatatime values (yes I know about the
> restriction of smalldatetime to range between 1900 and 2079).
> The odd thing is that even if i am updating a non-date field within the
> view, the above arithmetic error still occurs.
> My trigger looks like the following:
> --
> CREATE TRIGGER mytrigger ON mytableview
> INSTEAD OF UPDATE
> AS
> DECLARE @.mydate datetime
> SELECT @.mydate = GETDATE()
> UPDATE mytable SET
> field1 = ISNULL(inserted.field1, 0),
> field2 = ISNULL(inserted.field2, 0),
> field3 = ISNULL(inserted.field3, 0),
> date1 = inserted.date1,
> date2 = @.date+30
> FROM inserted
> WHERE mytable.userid = inserted.userid
> --
> Am i getting this error because i am mixing a date2 fiels (which is of
> type smalldatetime) with @.date (which is of type datetime) ?
> Any help most appreciated.
>|||Also, drop the SELECT @.mydate -- was just for testing. Basically using the
SMALLDATETIME data type instead of DATETIME.
HTH
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23tFJV0z0FHA.2312@.TK2MSFTNGP14.phx.gbl...
> Do you need the extra ms or time range? If not try:
> DECLARE @.mydate smalldatetime
> SELECT @.mydate = GETDATE()
> SELECT @.mydate
> HTH
> Jerry
> "PWalker" <pwalker@.nospam.com> wrote in message
> news:uCQYjxz0FHA.3256@.TK2MSFTNGP09.phx.gbl...
>|||Sorry, I meant to say:
mytable view has a number of *date* fields. All the *date* fields are of
type
smalldatatime.
.. late night
cheers, peter
"PWalker" <pwalker@.nospam.com> wrote in message
news:uCQYjxz0FHA.3256@.TK2MSFTNGP09.phx.gbl...
> Hi, I have a view set up with an INSTEAD OF UPDATE trigger specified.
> When I perform an update on certain records, i.e.:
> UPDATE mytableview
> SET field1 = 1
> WHERE userid = 1234
> I am finding that *some* user id's result in the follwing error:
> "Arithmetic overflow error converting expression to data type
> smalldatetime.
> The statement has been terminated."
> mytable view has a number of data fileds. All the data fields are of type
> smalldatatime.
> When i compare the user record of a userid that causes an error to one
> that doesnt cause an error, the dates do vary, where some date fields have
> NULL's or correctly formated smalldatatime values (yes I know about the
> restriction of smalldatetime to range between 1900 and 2079).
> The odd thing is that even if i am updating a non-date field within the
> view, the above arithmetic error still occurs.
> My trigger looks like the following:
> --
> CREATE TRIGGER mytrigger ON mytableview
> INSTEAD OF UPDATE
> AS
> DECLARE @.mydate datetime
> SELECT @.mydate = GETDATE()
> UPDATE mytable SET
> field1 = ISNULL(inserted.field1, 0),
> field2 = ISNULL(inserted.field2, 0),
> field3 = ISNULL(inserted.field3, 0),
> date1 = inserted.date1,
> date2 = @.date+30
> FROM inserted
> WHERE mytable.userid = inserted.userid
> --
> Am i getting this error because i am mixing a date2 fiels (which is of
> type smalldatetime) with @.date (which is of type datetime) ?
> Any help most appreciated.
>|||thanks ill try that when i get to work
I hope its as obvious as changing smalldatetime to datetime!
cheers, peter

> Also, drop the SELECT @.mydate -- was just for testing. Basically using
> the SMALLDATETIME data type instead of DATETIME.
> HTH
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23tFJV0z0FHA.2312@.TK2MSFTNGP14.phx.gbl...
>

HELP WITH A TRIGGER?

Hello.
Just curios about something.

I am trying to track down an error, and the details are boring, but what would help is this.
Can I do something in SQL Server 2005 that would inform me of ANY change in any field in any table in the database?

You should build a trigger in each table to do that.

You can check this also

User Tips: Receiving an Email When Database Data is Changed

Regards

Help with a Trigger.

from example a do this in Vb.
scColor.CommandText = "Update Product Set Color" & WColor.Trim & "=" &
WcolorValue & " Where Product='" & VarPubProduct & "'"
In a Trigger how can I know wich column is update Color01 or Color02 or
color03?
thanksUse the UPDATE() function [available in triggers]. This only lets you
know that the column indicated in the function was updated in at least
one row of the update. Then you'll need to compare the inserted and
deleted virtual tables to get the rows that were actually updated.
e.g.
IF UPDATE(Color01)
insert into LogTable (product, color01_old, color01_new)
select d.product, d.color01, i.color01
from deleted d
join inserted i on d.product = i.product
where d.color01 <> i.color01
Miguel Arenas wrote:
> from example a do this in Vb.
> scColor.CommandText = "Update Product Set Color" & WColor.Trim & "=" &
> WcolorValue & " Where Product='" & VarPubProduct & "'"
> In a Trigger how can I know wich column is update Color01 or Color02 or
> color03?
> thanks
>|||Thank you. This was very helpful.
"Trey Walpole" wrote:

> Use the UPDATE() function [available in triggers]. This only lets you
> know that the column indicated in the function was updated in at least
> one row of the update. Then you'll need to compare the inserted and
> deleted virtual tables to get the rows that were actually updated.
> e.g.
> IF UPDATE(Color01)
> insert into LogTable (product, color01_old, color01_new)
> select d.product, d.color01, i.color01
> from deleted d
> join inserted i on d.product = i.product
> where d.color01 <> i.color01
>
> Miguel Arenas wrote:
>

Help with a trigger

Hello
Im am a sql newbie who has a question concerning triggers.
Ive made this trigger:

create trigger check_if_already_exists on
users
for insert ,update
as
begin
begin transaction

Declare @.Username varchar(20)
Declare @.Username_exists varchar(20)

select @.Username = user_id
from inserted

set @.Register = cursor scroll dynamic
for select user_id,
from users
order by user_id

open @.Register

fetch next from @.Register into @.Username_exists

while @.@.fetch_status = 0
begin

if(@.Username = @.Username_exists)

begin
rollback transaction
print'Transaction rollback'
end

fetch next from @.Register into @.Username_exists

end

close @.Register
deallocate @.Register

commit transaction
end

which takes a variable from inserted and checks it with the existing one
in the database through a cursor.

My questions are
1)
to start and end the trigger should i use:
begin transaction -- end transaction | commit transaction

or
begin -- end

2)
Im using a cursor here , is this a good use of cursors

Mike[posted and mailed, please reply in public]

(timmy_dale12@.hotmail.com) writes:
> select @.Username = user_id
> from inserted

Note that since a trigger fires once per statement, the inserted table
can hold more than one row. Only getting one value to a variable is
not a good thing.

> to start and end the trigger should i use:
> begin transaction -- end transaction | commit transaction

When you detect an error situation, you should issue a ROLLBACK TRANSACTION.
You should not fiddle with BEGIN/COMMIT TRANSACTION in a trigger. A trigger
always executes in the context of a transaction, as it is part of a INSERT,
DELETE or UPDATE statement and such a statement always starts a transaction,
if there is no transaction already active.

> Im using a cursor here , is this a good use of cursors

No, it is not.

There are actually a whole bunch of problems with your trigger, and which
leads to that this trigger should not exist at all.

Let's first look at the test you should make. This is a set-based version
of your cursor loop which covers all inserted rows:

IF EXISTS (SELECT *
FROM inserted i
JOIN users u ON i.user_id = u.user_id)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('One or more inserted users does already exist', 16, -1)
RETURN
END

Note here that I use RAISERROR rather than PRINT. This is because I
want the client to beware of that there was an error.

However, since all rows in inserted at this point also are in users,
this check is always going to be true, so you will always get an error
message. Thus, you cannot implement this check in a trigger at all.
Rather you should have a UNIQUE or PRIMARY KEY constraint on the user_id
column.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>
> IF EXISTS (SELECT *
> FROM inserted i
> JOIN users u ON i.user_id = u.user_id)
> BEGIN
> ROLLBACK TRANSACTION
> RAISERROR('One or more inserted users does already exist', 16, -1)
> RETURN
> END
> Note here that I use RAISERROR rather than PRINT. This is because I
> want the client to beware of that there was an error.

How can i get the error message that the RAISERROR method generates,
is it returned in any way.
For example if i use a java application can i catch this in a try
block:

try {

......
}catch(SQLException e){
e.toString();
}|||(timmy_dale12@.hotmail.com) writes:
> How can i get the error message that the RAISERROR method generates,
> is it returned in any way.
> For example if i use a java application can i catch this in a try
> block:
> try {
> ......
> }catch(SQLException e){
> e.toString();
> }

That depends on the client library you are using. Since all I know about
Java is that it lies in the vicinity of Sumatra, I cannot say for sure
what happens, but I would expect an exception to be thrown, yes. (Provided
that you for the severity level specify 11 or higher.)

What can be puzzling is if the SQL code generates result sets, before
the RAISERROR statement, then you need to get past the result sets before
the exception is thrown. (Whether this applies to Java, I don't know,
but it happens with ADO.) Furthermore, there are more result sets you
may expect, because these "2 rows affected" you can see in Query Analyzer
from an INSERT, UPDATE or DELETE statement is also some sort of result
set.

SET NOCOUNT ON removes these "result sets", and is generally good for
performance, so use this.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Help with a trigger

I have written the following trigger

alter trigger updatecustomerorderreleasedatewhenstatusisreleased
on CUSTOMER_ORDER
after update
as

declare
@.CUST_ORDER_ID VARCHAR(15)

select @.CUST_ORDER_ID = ID from customer_order
if update(STATUS)
insert into CUSTER_ORDER_RELEASE_DATE
(CUSTOMER_ORDER_ID,RELEASE_DATE)
values (@.CUST_ORDER_ID,getdate())

It always returns the same ID from customer_order not the one that is
changing status. What am I missing?[posted and mailed, please reply in news]

ANDY PHILLEY (APHILLEY@.WATSONFURNITURE.COM) writes:
> alter trigger updatecustomerorderreleasedatewhenstatusisreleased
> on CUSTOMER_ORDER
> after update
> as
> declare
> @.CUST_ORDER_ID VARCHAR(15)
> select @.CUST_ORDER_ID = ID from customer_order
> if update(STATUS)
> insert into CUSTER_ORDER_RELEASE_DATE
> (CUSTOMER_ORDER_ID,RELEASE_DATE)
> values (@.CUST_ORDER_ID,getdate())
> It always returns the same ID from customer_order not the one that is
> changing status. What am I missing?

You are reading the customer_order table with out a WHERE clause. SQL
Server will retrieve all rows, and the variable with recieve the last
value returned. Since there is no ORDER BY clause, this can be any
value in the table, although it's not truly random. It is not unlikely
that it is always the same row.

If you want to know rows that were affected, you should use the
the virtual tables "inserted" and "deleted". They hold the the after-
image and the before-image respectivly of the updated rows. Note that
since a trigger fire once per statement, there can be several rows in
the tables.

Your trigger should probably read:

CREATE TRIGGER trg ON CUSTOMER_ORDER AFTER UPDATE AS
IF UPDATE(STATUS)
INSERT CUSTOMER_ORDER_RELEASE_DATE (CUSTOMER_ORDER_ID, RELEASE_DATE)
SELECT i.ID, getdate()
FROM inserted i
JOIN deleted d ON i.ID = d.ID
WHERE i.STATUS = 'Relased'
AND d.STATUS = 'Something else'

I've here also taken care of the case that Status may be updated, but
not change.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Help Trigger

i want to audit transactions done to table TOrig.
I created table TAudit same as TOrig in addition to ActionID (1 for
insert, 2 for update, 3 for delete), System Date and System User.
I created triggers on TOrigto insert into TAudit in case of insert,
update & delete.

TOrig contains text column. So i created instead of Trigger like:

Create TRIGGER TrigDelete
ON dbo.TOrig
Instead of Delete
AS
declare @.id int
select @.id=Liq_ID from Deleted

If Not Exists (Select * from TAudit where Liq_ID=@.id and ActionID=3 )
BEGIN
Insert into TAudit
select * , 3, GetDate(), System_USer
from Deleted

Delete from TOrig where Liq_ID=@.id
END
GO

PROBLEM: is that Torig is a detail table to a master table and has a
cascade delete relationship with the master table.
So Instead Trigger does it work. What do I DO??On 4 Oct 2004 06:21:50 -0700, Marie-Christine wrote:

>PROBLEM: is that Torig is a detail table to a master table and has a
>cascade delete relationship with the master table.
>So Instead Trigger does it work. What do I DO??

Hi Marie-Christine,

1. Create the trigger as an AFTER trigger, not as an INSTEAD OF trigger.

2. Rewrite the trigger, so that it will handle multi-rows inserts, updates
and deletes as well.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||
I would've liked to use For Delete trigger but i can't since there are
text columns in the table. Neither can i use After Trigger. And i can't
retrieve rows from Deleted table when there are text columns. That's why
i used Instead of Delte trigger. But now i'm hung cause Instead of
Delete trigger doesn't work with Cascade. That's why i'm confused now.
There are 2 problems: text columns in the table, and cascade delete. How
do i solve the 2 problems?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||
ANyone!!! no solution???

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||MC <anonymous@.discussions.microsoft.com> wrote in message news:<4163a5d8$0$26140$c397aba@.news.newsgroups.ws>...
> ANyone!!! no solution???
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

If you can't use a trigger, you could put the DELETE and the audit
logic into a procedure, then make users and applications use the
procedure for all deletions.

Simon