Troubles on After Delete Trigger on SQL Server


So, last week, I stumbled upon an error in an application used frequently in the company I am working for. The problem occurred when executing a statement to delete data preventing redundancies when uploading a new batch of data. Sure, my team was appointed to fix the error since I am working as a support in the company.

When we were trying to debug, we got the error stating that the SQL Exception because the subquery returned more than one value. That was quite frustrating since the command we tried to execute was a simple delete command without using subquery. The frustating part was not that we did not understand the error, but because we did not have access to see what was inside the trigger.

So after we get the access to the trigger, we could understand that the subquery was to select data from DELETED temporary table. Unfortunately, the DELETED table result from query returns many rows and cause the error. That time I did not quite understand why. But after reading the MSDN entry about Using the Insterted and Deleted Tables, I understand the problem.

The statement to delete is

DELETE
FROM [dbo].[dailyUpload]
WHERE DATEDIFF(d, [uploadDate], GETDATE()) = 0

Meanwhile inside the trigger,

DELETE
FROM [dbo].[someAnother]
WHERE DATEDIFF(d, 
    [uploadDate], 
    (SELECT [uploadDate] 
        FROM DELETED)) = 0
    AND [IDDaily] = 
    (SELECT [ID] 
     FROM DELETED)

Surely, the subquery inside the trigger would return so many data since there were thousands of data uploaded to dailyUpload table.

So, I came up on a solution, storing the deleted value then delete it through looping in application side. Why not database side? Since the database was used by many users, it would not be fair to use the resource for some faulty of a user. Here is the code in C#.

// get the data
dailyUploadTableAdapter
    .FillByUploadDate(dailyUploadDataTable,
        DateTime.Now);
// delete data
/** OPTION A **/
foreach(var singleRow 
    in dailyUploadTableDataTable.Rows){
    dailyUploadTableAdapter
        .DeleteByUploadDate(singleRow.uploadDate);
    /** OPTION B will not need the line below **/
    someAnotherTableAdapter
        .DeleteByDUIDAndUploadDate(singleRow.ID,
            singleRow.uploadDate);
}

In the little example above, there are two alternatives. Alternative A will not need the trigger anymore. In my case, we cannot disable the trigger, that is why there is solution B. Option B will stick with the trigger rule.

So I think you can come up with either the solution. Anyway, if there is another way to process, please kindly share and we can talk it here!

Clustered dan Non-clustered Index


Well, lagi lagi database. Ini sebenarnya tugas aku dikasi sama dosen Database System aku di PI Del. Kalian taulah PI Del di mana…

Pertama, index itu apa? Itu cara untuk pencarian ke tabel. Gini, kalau kita baca buku, kan bisa liat index. Kalau kita baca per halaman kan lelet nyarinya… Nah, index di tabel berarti serupa. Kita pasang index biar gampang cari record tertentu.

Nah, kalau saja kita membuat index ke satu tabel, kita kan bisa milih, clustered atau nonclustered. Ingat juga kalau clustered hanya bisa satu pada satu tabel, sementara nonclustered bisa lebih.

Kalau membuat index, kita disertai banyak pertimbangan. Misalnya, apakah waktu eksekusi bertambah atau berkurang kalau kita tambah index. Kalau sama aja atau lebih, bagusan engga kan? Terus, kalau buat index, itu kan memperbesar size tabel. Maksudnya size di disk. Jadi kalau datanya besar dan indexnya banyak, kan satu tabel makan banyak tempat tuh. Akhirnya kita pun milih, mana yang mau dipakai, clustered atau non clustered.

Clustered hanya bakal membuat diri kita mudah untuk mencari data pada tabel yang bakalan mereturn satu resultset dengan size besar. Gimana maksudnya? Kalau misalnya kolomnya banyak, rownya banyak yah pake clustered, kalau engga yah pakai saja yang non-clustered.

Well, itu aja dulu. Lain kali kita lanjut. Moga berguna. 🙂

Posted with WordPress for BlackBerry.