Is this correct?

Hi, This is difficult for me since I know very little about triggers, tables and Databases in general. But... as sometimes happens during my job I've been assigned something out of my league. Now... I've been reading and trying to "reuse" examples to do what I'm trying to do.
So, let me first explain what I'm trying to do. We have this application that runs on Solaris and uses Syabase. The user ID's are defined on a Sybase table (adb.tssuser) and on the Sun OS. I have this software that can automatically create records on both, but just that. So, I can add a user on Solaris and create a home directory. Then I can insert a record on a sybase table. What this software can't do is execute scripts or do file system operations. So, what I have in mind is that after succesfull creation on the OS and on this intermediate table a trigger would trigger and run a script that would do the rest. The script has to be called with the userid and the cat fields added. Keep in mind that I'll be writing to a intermediate table and then, with a trigger, updating the definitve table (adb.tssuser). This is what I've come up to:


USE db
SET CHAINED OFF

CREATE TABLE emp
(
      empno            NUMERIC(8)      IDENTITY,
      usrid            NVARCHAR(64) NULL,
      cat            CHAR(64) NULL,
      CONSTRAINT pk_empno PRIMARY KEY (empno),
)
GO


/* holds last generated identity value */
CREATE TABLE empno
(
      empno      NUMERIC(8)      NOT NULL,
      CONSTRAINT unq_empno UNIQUE (empno)
)
INSERT INTO empno VALUES(-1)
GO

CREATE PROCEDURE sp_empno
      @io_empno      NUMERIC OUTPUT,
      @i_userid       VARCHAR
AS

DECLARE @key NUMERIC(8)

IF (@io_empno IS NULL)
BEGIN

      DECLARE rs CURSOR FOR SELECT empno FROM empno
      OPEN rs
      FETCH rs INTO @key
      IF(@key = -1)
            SELECT @io_empno = NULL
      ELSE
            SELECT @io_empno = @key
      CLOSE rs
      DEALLOCATE CURSOR rs
END
GO
sp_procxmode sp_empno, "anymode"
GO


/****************************************************************************/
/* Create event log table.                                                  */
/****************************************************************************/

CREATE TABLE eventlog
(
      record_id      NUMERIC(38)      IDENTITY
                              NOT NULL,
      table_key      VARCHAR(64)      NOT NULL,
      status      CHAR(1)      DEFAULT 'N',
      event_type      NUMERIC(1)      NOT NULL
                              CHECK (event_type IN (1, 2, 3, 4, 5, 6, 7, 8)),
      event_time      DATETIME      DEFAULT getDate()
                              NOT NULL,
      perpetrator      VARCHAR(64)      DEFAULT USER
                              NULL,
      table_name      VARCHAR(64)      NOT NULL,
      column_name      VARCHAR(64) NULL,
      old_value      NVARCHAR(64) NULL,
      new_value      NVARCHAR(64) NULL
)

CREATE UNIQUE NONCLUSTERED INDEX i_eventlog_1 ON eventlog(table_key, record_id, status)
CREATE UNIQUE NONCLUSTERED INDEX i_eventlog_2 ON eventlog(record_id, table_key)
GO


CREATE TRIGGER t_emp_insert
      ON emp
      FOR INSERT
AS

DECLARE
      @opcode            SMALLINT,
      @date_style       SMALLINT,
      @table_name       VARCHAR(64),
      @key                   VARCHAR(64),
      @new_empno            VARCHAR(64)

      UPDATE empno SET empno = (SELECT new.empno FROM inserted new)

      SELECT @opcode       = 5      /* Insert (Query-Back) */
      SELECT @date_style = 109  /* mon dd yyy hh:mm:sss AM (or PM) */
      SELECT @table_name = 'emp'

      SELECT @new_empno = LTRIM(STR(new.empno)) from inserted new
      SELECT @key = "empno=" + @new_empno

      INSERT INTO dbo.eventlog(event_type, table_name, table_key)
            VALUES(@opcode, @table_name, @key)
GO

CREATE TRIGGER t_emp_delete
    ON emp
    FOR DELETE

AS

      INSERT INTO dbo.eventlog(event_type, table_name, table_key)
            (SELECT 4, 'emp', 'empno=' + LTRIM(STR(old.empno)) from deleted old)
GO
CREATE TRIGGER t_emp_scp_add
      ON emp
      FOR INSERT
AS

DECLARE
      @opcode            SMALLINT,
      @date_style       SMALLINT,
      @table_name       VARCHAR(64),
      @key                   VARCHAR(64),
      @new_empno            VARCHAR(64)

      SELECT @opcode       = 5      /* Insert (Query-Back) */
      SELECT @date_style = 109  /* mon dd yyy hh:mm:sss AM (or PM) */
      SELECT @table_name = 'emp'

      SELECT @new_empid = STR(new.userid) from inserted new
      SELECT @categ = STR(new.cat) from inserted new
        select @script = "/home/nvl/oss_novell.sh " + @new_empid + " " + @categ
        execute xp_cmdshell @script
GO


Thanks for your help!!!
Nico
LVL 3
nickoargAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nickoargAuthor Commented:
Note that there might be things and commands that shouldn't and things that do nothing... this is due to my lack of DB knowledge...
0
ChrisKingCommented:
your triggers are written as if you will only ever be processing one row at a time (common mistake)

you should also know that xp_cmdshell can not be rolled back, so :

begin tran
insert into emp ...
rollback

will cal the /home/nvl/oss_novell.sh script but you won't have the row in te database
0
nickoargAuthor Commented:
The idea is that only one row at a time can be inserted, never more than 1 in one operation.

..."but you won't have the row in te database"...
Why? Does the trigger acts before the row is inserted in the table "emp"?

"begin tran
insert into emp ...
rollback"
please, note that I really don't know about databases... I've base all that on examples and existing code... where do I put that? how should it look to be correct?
thanks
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

ChrisKingCommented:
> The idea is that only one row at a time can be inserted, never more than 1 in one operation.
that idea might be OK for this week, but next month or next year or later, someone will insert more than one row and the triggers will not function as expected

to protect yourself (asssuming you don want to rewrite them), put the following code into the top of the triggers.

IF @@rowcount > 1
  BEGIN
    ROLLBACK TRIGGER WITH RAISERROR <put your error details here>
    RETURN
  END

> Why? Does the trigger acts before the row is inserted in the table "emp"?
the trigger does not  act before the row is inserted, the rollback will force everything that was just inserted/updated/deleted to be undone, but you can not undo a execution of a unix script. This is a known limitation of the xp commands.
0
nickoargAuthor Commented:
"someone will insert more than one row and the triggers will not function as expected"
no..., the updates come from a software that updates only one row at a time, but I undestand what you say.

I still don't understand the rollback...
step 1, a new row is inserted in the table emp
step 2, the trigger t_emp_scp_add fires away and the xp_cmdshell would run my script

why do I want to remove new the row from table emp? (assuming that that is what rollback does)
0
ChrisKingCommented:
in simple terms ROLLBACK means don't keep the changes I just made, whereas COMMIT means keep the changes
To better understand it, read up on "transaction control"

try the following script:

CREATE TABLE #test (num int NOT NULL)

BEGIN TRAN
INSERT INTO #test SELECT 1
INSERT INTO #test SELECT 2
INSERT INTO #test SELECT 3
COMMIT
SELECT * FROM #test                     -- will show 1, 2 and 3

BEGIN TRAN
INSERT INTO #test SELECT 4
INSERT INTO #test SELECT 5
SELECT * FROM #test                     -- will show 1, 2, 3, 4 and 5
ROLLBACK
SELECT * FROM #test                     -- will show 1, 2 and 3

BEGIN TRAN
UPDATE #test SET num = num + 100
SELECT * FROM #test                     -- will show 101, 102 and 103
ROLLBACK
SELECT * FROM #test                     -- will show 1, 2 and 3

BEGIN TRAN
DELETE FROM #test
SELECT * FROM #test                     -- will show no rows
ROLLBACK
SELECT * FROM #test                     -- will show 1, 2 and 3

DROP TABLE #test
go

===============
actual script output
===============
(1 row affected)
(1 row affected)
(1 row affected)
 num
 -----------
           1
           2
           3

(3 rows affected)
(1 row affected)
(1 row affected)
 num
 -----------
           1
           2
           3
           4
           5

(5 rows affected)
 num
 -----------
           1
           2
           3

(3 rows affected)
(3 rows affected)
 num
 -----------
         101
         102
         103

(3 rows affected)
 num
 -----------
           1
           2
           3

(3 rows affected)
(3 rows affected)
 num
 -----------

(0 rows affected)
 num
 -----------
           1
           2
           3

(3 rows affected)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ChrisKingCommented:
I am suprised:
- this question was not even pinged
- and no answer is actually marked as accepted
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.