I'm toying with the idea of using a perl script to generate triggers in my
SQLserver database.
First, is this a reasonable idea?
If it's reasonable to build triggers this way, what escaping do I need to do
to get the following create trigger statement to work? It currently fails
complaining about declaring the variable @ and I can't figure out how to
escape it....
My platform is NT, ActivePerl 633,DBD-ODBC...database is SQLServer2000.
TIA,
Brien
use DBI;
my $dbh = DBI->connect('DBI:ODBC:DEV_OST', 'user', 'pass',
{PrintError => 1});
my $st_create_trigger = $dbh->do(qq{
create TRIGGER trg_CDSAP_2_1_0_central_err
ON CDSAP_2_1_0
FOR update AS
if @@rowcount=1
DECLARE @badid NUMERIC(12,0)
DECLARE @status NUMERIC(3,0)
DECLARE @message varchar(255)
DECLARE @errorid NUMERIC(12,0)
if update(status)
BEGIN
SELECT @badid=seq_no,
@status=status,
@message=error_str
FROM inserted
if @status in (0,1,2)
return
select @errorid = max(ID) + 1 from WASP_Support.dbo.tblErrors
where ID > 500000000000
if @errorid is null
select @errorid=500000000001
insert into WASP_Support.dbo.tblErrors
( ID,
ErrRowID,
SourceID,
Attribute,
AttributeValue,
MappingName,
ConstraintName,
SessionID,
ErrorCode,
ErrorDescription,
ErrorDate)
values(
@errorid,
@badid,
@badid,
'OST EBE Table: CDSAP_2_1_0',
'OST Rule: WASP_Expense_Daily_STP',
'OST EBE',
'OST EBE',
@badid,
'E',
@message,
getdate()
)
end
});
CONFIDENTIALITY AND DISCLAIMER NOTICE
The information contained in this e-mail and any attachments
is confidential and intended for the addressee only and may
be protected by copyright. Copying or forwarding any part of
this e-mail is prohibited. If you have received this e-mail in
error please delete it and notify us by telephone or return
e-mail immediately. Any views or opinions contained in
this message are solely those of the sender and do not
necessarily represent those of Microgen-OST Limited.
We advise you to carry out your own virus check before
opening any attachments as we do not accept liability for
any damage sustained as a result of any software viruses.
Microgen-OST Limited registered address:
11, Park Street,
Windsor,
Berkshire
SL4 1LU
Company Registration No 3475849
