Hi,

I'm trying to work around a bug that our helpdesk software has. When a new
issue is created, it cannot automatically default 2 fields to the value of
No like we need it to.

I have a field called "Audited" and one called "Billed to Client". When a
new issue is openned, it just leaves the value as Null in the database
instead of a value of No.

I would like to create a stored procedure and schedule it to run every 10
minutes to change any value of Null in those columns to No.

Database: bridgetrak
Table: Issues
Column: Audited
Column: Billed

If someone could help me out that would be great! I just don't have very
much experience with SQL statements.

Please email me at shawnf@sccnet.com

Thanks,
Shawn

Hi Shawn,

A much better way would be to set up default values of "No" for those
columns and disallow nulls. You can do this through Enterprise manager or
through an Alter Table Query. A stored procedure for this situation is
unnecessary and highly unadvisable.


Regards,

Tyler
"Shawn Fletcher" <shawnf@sccnet.com> wrote in message
news:40f6a897$0$49110$8f4e7992@newsreader.goldenga te.net...
> Hi,
>
> I'm trying to work around a bug that our helpdesk software has. When a
new
> issue is created, it cannot automatically default 2 fields to the value of
> No like we need it to.
>
> I have a field called "Audited" and one called "Billed to Client". When a
> new issue is openned, it just leaves the value as Null in the database
> instead of a value of No.
>
> I would like to create a stored procedure and schedule it to run every 10
> minutes to change any value of Null in those columns to No.
>
> Database: bridgetrak
> Table: Issues
> Column: Audited
> Column: Billed
>
> If someone could help me out that would be great! I just don't have very
> much experience with SQL statements.
>
> Please email me at shawnf@sccnet.com
>
> Thanks,
> Shawn
>
>

Thank you for your help Tyler,

I tried your suggestion, however after doing that, the helpdesk software
would not save a new issue so I had to change it back to allow nulls and
undo the default value of No. That would have worked great if the program
didn't suck.

Any other suggestions? The only work around I can think of is the stored
procedure. It's much better then currently connecting with Access and doing
a Search and replace.

Thanks,
Shawn


"Tyler Hudson" <TylerH@Spam.MeNOTallpax.com> wrote in message
news:cd6h0a$7i4$1@news.datasync.com...
> Hi Shawn,
>
> A much better way would be to set up default values of "No" for those
> columns and disallow nulls. You can do this through Enterprise manager or
> through an Alter Table Query. A stored procedure for this situation is
> unnecessary and highly unadvisable.
>
>
> Regards,
>
> Tyler
> "Shawn Fletcher" <shawnf@sccnet.com> wrote in message
> news:40f6a897$0$49110$8f4e7992@newsreader.goldenga te.net...
> > Hi,
> >
> > I'm trying to work around a bug that our helpdesk software has. When a
> new
> > issue is created, it cannot automatically default 2 fields to the value
of
> > No like we need it to.
> >
> > I have a field called "Audited" and one called "Billed to Client". When
a
> > new issue is openned, it just leaves the value as Null in the database
> > instead of a value of No.
> >
> > I would like to create a stored procedure and schedule it to run every
10
> > minutes to change any value of Null in those columns to No.
> >
> > Database: bridgetrak
> > Table: Issues
> > Column: Audited
> > Column: Billed
> >
> > If someone could help me out that would be great! I just don't have
very
> > much experience with SQL statements.
> >
> > Please email me at shawnf@sccnet.com
> >
> > Thanks,
> > Shawn
> >
> >
>
>

It sounds as though the helpdesk software is specifying insert values of
Null for those columns. If you can edit the helpdesk software, I would go
that route. If not, try using a trigger.

CREATE TRIGGER Issues_INSUPD
ON Issues
FOR INSERT, UPDATE
AS

UPDATE Issues
SET Audited = 'No'
WHERE
Audited IS NULL AND
<keyfieldgoeshere> IN (SELECT <keyfieldgoeshere> FROM INSERTED)

UPDATE Issues
SET Billed = 'No'
WHERE
Billed IS NULL AND
<keyfieldgoeshere> IN (SELECT <keyfieldgoeshere> FROM INSERTED)


"Shawn Fletcher" <shawnf@sccnet.com> wrote in message
news:40f8031e$0$63722$8f4e7992@newsreader.goldenga te.net...
> Thank you for your help Tyler,
>
> I tried your suggestion, however after doing that, the helpdesk software
> would not save a new issue so I had to change it back to allow nulls and
> undo the default value of No. That would have worked great if the program
> didn't suck.
>
> Any other suggestions? The only work around I can think of is the stored
> procedure. It's much better then currently connecting with Access and
doing
> a Search and replace.
>
> Thanks,
> Shawn
>
>
> "Tyler Hudson" <TylerH@Spam.MeNOTallpax.com> wrote in message
> news:cd6h0a$7i4$1@news.datasync.com...
> > Hi Shawn,
> >
> > A much better way would be to set up default values of "No" for
those
> > columns and disallow nulls. You can do this through Enterprise manager
or
> > through an Alter Table Query. A stored procedure for this situation is
> > unnecessary and highly unadvisable.
> >
> >
> > Regards,
> >
> > Tyler
> > "Shawn Fletcher" <shawnf@sccnet.com> wrote in message
> > news:40f6a897$0$49110$8f4e7992@newsreader.goldenga te.net...
> > > Hi,
> > >
> > > I'm trying to work around a bug that our helpdesk software has. When
a
> > new
> > > issue is created, it cannot automatically default 2 fields to the
value
> of
> > > No like we need it to.
> > >
> > > I have a field called "Audited" and one called "Billed to Client".
When
> a
> > > new issue is openned, it just leaves the value as Null in the database
> > > instead of a value of No.
> > >
> > > I would like to create a stored procedure and schedule it to run every
> 10
> > > minutes to change any value of Null in those columns to No.
> > >
> > > Database: bridgetrak
> > > Table: Issues
> > > Column: Audited
> > > Column: Billed
> > >
> > > If someone could help me out that would be great! I just don't have
> very
> > > much experience with SQL statements.
> > >
> > > Please email me at shawnf@sccnet.com
> > >
> > > Thanks,
> > > Shawn
> > >
> > >
> >
> >
>
>

Shawn,

Try this:

create table Issues(Audited char(3), Billed char(3))
go

CREATE TRIGGER TRIGG1 ON Issues
INSTEAD OF INSERT
AS BEGIN
INSERT Issues
SELECT IsNull(Audited, 'No'), IsNull(Billed, 'No')
FROM inserted
END
go

insert Issues values(null, null)
select * from Issues

Shervin


"Shawn Fletcher" <shawnf@sccnet.com> wrote in message news:<40f8031e$0$63722$8f4e7992@newsreader.goldeng ate.net>...
> Thank you for your help Tyler,
>
> I tried your suggestion, however after doing that, the helpdesk software
> would not save a new issue so I had to change it back to allow nulls and
> undo the default value of No. That would have worked great if the program
> didn't suck.
>
> Any other suggestions? The only work around I can think of is the stored
> procedure. It's much better then currently connecting with Access and doing
> a Search and replace.
>
> Thanks,
> Shawn
>
>
> "Tyler Hudson" <TylerH@Spam.MeNOTallpax.com> wrote in message
> news:cd6h0a$7i4$1@news.datasync.com...
> > Hi Shawn,
> >
> > A much better way would be to set up default values of "No" for those
> > columns and disallow nulls. You can do this through Enterprise manager or
> > through an Alter Table Query. A stored procedure for this situation is
> > unnecessary and highly unadvisable.
> >
> >
> > Regards,
> >
> > Tyler
> > "Shawn Fletcher" <shawnf@sccnet.com> wrote in message
> > news:40f6a897$0$49110$8f4e7992@newsreader.goldenga te.net...
> > > Hi,
> > >
> > > I'm trying to work around a bug that our helpdesk software has. When a
> new
> > > issue is created, it cannot automatically default 2 fields to the value
> of
> > > No like we need it to.
> > >
> > > I have a field called "Audited" and one called "Billed to Client". When
> a
> > > new issue is openned, it just leaves the value as Null in the database
> > > instead of a value of No.
> > >
> > > I would like to create a stored procedure and schedule it to run every
> 10
> > > minutes to change any value of Null in those columns to No.
> > >
> > > Database: bridgetrak
> > > Table: Issues
> > > Column: Audited
> > > Column: Billed
> > >
> > > If someone could help me out that would be great! I just don't have
> very
> > > much experience with SQL statements.
> > >
> > > Please email me at shawnf@sccnet.com
> > >
> > > Thanks,
> > > Shawn
> > >
> > >
> >
> >